Postgresql - Selecting data with full join skips some rows

Alexander
February 22, 2024
183 views
2 votes
3 Answers

I just want to thank anyone that would like to spent some of his time to address my question. I just want to mention that I am not so good at SQL.
Description of the problem.
I have Orders table where each order has price, userId who made the order and date of the order.
Also I have payments table where there is userid, date and payment amount.
All I need is getting the order and payments for each user grouped by years (I have a case to group by months but lets put it aside for now)

The problem arise when the client has no orders for 2024 but has payments for the period. than those rows are skipped.
Putting the sql query I use.

SELECT order_data.user_id,
       order_data.username,
       order_data.customer_email,
       order_data.customer_mobile,
       order_data.year                                             as year,
       order_data.orders_amount                                    as order_amount,
       COALESCE(payment_data.payment_amount, 0)                    as payment_amount,
       COALESCE(payment_amount - orders_amount, 0 - orders_amount) as balance,
       order_data.latest_order_date,
       payment_data.latest_payment_date
FROM (SELECT orders.user_id,
             orders.username,
             orders.customer_email,
             orders.customer_mobile,
             date_part('year', orders.status_change_date) as year,
             sum(orders.amount)                           as orders_amount,
             max(orders.create_date)                      as latest_order_date
      from arag_araqum.orders orders
      GROUP BY orders.user_id, orders.username, orders.customer_email, orders.customer_mobile, year) as order_data
         FULL JOIN (Select payment.user_id,
                           date_part('year', payment.create_date) as year,
                           sum(payment.amount)                    as payment_amount,
                           max(payment.create_date)               as latest_payment_date
                    from arag_araqum.payments as payment
                    GROUP BY user_id, year) as payment_data
                   ON order_data.user_id = payment_data.user_id
                       AND
                      order_data.year = payment_data.year;

ORDERS DB

create table orders
(
    id                 bigint generated always as identity
        constraint order_pkey
            primary key,
    user_id            text not null,
    username           text not null,
    customer_email     text not null,
    customer_mobile    text,
    amount             bigint,
    create_date        date,
    status_change_date date
);

PAYMENTS TABLE

create table payments
(
    id          bigint generated always as identity
        constraint payment_pkey
            primary key,
    user_id     text not null,
    amount      bigint,
    create_date date
);

The case is when user A has no orders for 2024 but has payments made

Tags: postgresql sql

Answers

Solution to your problem:

SELECT COALESCE(order_data.user_id,payment_data.user_id) AS user_id,
       COALESCE(order_data.username,payment_data.username) AS username,
       COALESCE(order_data.customer_email,payment_data.customer_email) AS customer_email,
       COALESCE(order_data.customer_mobile,payment_data.customer_mobile) as customer_mobile,
       COALESCE(order_data.year,payment_data.year) as year,
       COALESCE(order_data.orders_amount,0) as order_amount,
       COALESCE(payment_data.payment_amount, 0) as payment_amount,
       COALESCE(payment_amount,0) - COALESCE(orders_amount, 0) as balance,
       order_data.latest_order_date,
       payment_data.latest_payment_date
FROM 
(SELECT orders.user_id,
        orders.username,
        orders.customer_email,
        orders.customer_mobile,
        date_part('year', orders.status_change_date) as year,
        sum(orders.amount) as orders_amount,
        max(orders.create_date) as latest_order_date
 FROM arag_araqum.orders AS orders
 GROUP BY orders.user_id, orders.username, orders.customer_email, orders.customer_mobile, year
) as order_data
FULL JOIN 
(Select payment.user_id,
        orders.username,
        orders.customer_email,
        orders.customer_mobile,
        date_part('year', payment.create_date) as year,
        sum(payment.amount) as payment_amount,
        max(payment.create_date) as latest_payment_date
 FROM arag_araqum.payments as payment
 LEFT JOIN 
 (SELECT DISTINCT user_id,
                  username,
                  customer_email,
                  customer_mobile
  FROM arag_araqum.orders
 ) AS orders
 ON orders.user_id = payment.user_id
 GROUP BY payment.user_id, orders.username,orders.customer_email,orders.customer_mobile,year
) as payment_data
ON order_data.user_id = payment_data.user_id
AND
order_data.year = payment_data.year;

It is hard to answer without some sample data and expected result from that data, so I’ll try to show what could be the issue you are facing – with some sample data like here:

--    S a m p l e    D a t a :
Create Table orders(USER_ID Int, ORDER_DATE DATE, ORDER_AMOUNT Decimal);
Insert Into orders VALUES
(1, DATE '2023-07-15', 105.5), 
(1, DATE '2023-11-15', 194.5),
(1, DATE '2024-01-15', 100),
(2, DATE '2023-08-10', 100), 
(2, DATE '2023-09-10', 100),
(2, DATE '2024-10-10',  50),
(3, DATE '2024-01-20', 1000);

Create Table payments (USER_ID Int, PAYMENT_DATE DATE, PAYMENT_AMOUNT Decimal);
Insert Into payments VALUES
(1, DATE '2023-12-01', 60), 
(2, DATE '2023-12-02', 50), 
(3, DATE '2023-12-03', 40);

With orders and payments from above – if we do the FULL JOIN as in your query…

--    Your query FULL JOIN
Select o.USER_ID as ORDER_USER_ID, o.ORDER_DATE, o.ORDER_AMOUNT, 
       Extract(YEAR From o.ORDER_DATE) as ORDER_YEAR, 
       p.USER_ID as PAYMENT_USER_ID, p.PAYMENT_AMOUNT, 
       Extract(YEAR From p.PAYMENT_DATE) as PAYMENT_YEAR
From   orders o
Full Join payments p ON(p.USER_ID = o.USER_ID And
                        Extract(YEAR From p.PAYMENT_DATE) = Extract(YEAR From o.ORDER_DATE)
                       );

… the result would be …

/*    R e s u l t :
order_user_id   order_date    order_amount  order_year  payment_user_id  payment_amount  payment_year
-------------   -----------   ------------  ----------  ---------------  --------------  ------------   
            1   2023-07-15           105.5        2023                1              60          2023
            1   2023-11-15           194.5        2023                1              60          2023
            1   2024-01-15             100        2024             null            null          null
            2   2023-08-10             100        2023                2              50          2023
            2   2023-09-10             100        2023                2              50          2023
            2   2024-10-10              50        2024             null            null          null
         null   null                  null        null                3              40          2023
            3   2024-01-20            1000        2024             null            null          null    */

As I understood the question it looks like you want your data of all orders/payments with the balance grouped by months (here by years) – regardless if in any particular month (year) there were both orders and payments or just one of them.
One option to do it (with sample data from above) is to group the orders and payments separately and do the UNION ALL. After that just aggregate and group by the resultset of the unioned data:

SELECT USER_ID, A_YEAR, Sum(ORD_AMOUNT) as ORD_AMOUNT, Sum(PAY_AMOUNT) as PAY_AMOUNT, 
       Sum(ORD_AMOUNT) - Sum(PAY_AMOUNT) as BALANCE
FROM ( Select USER_ID, Extract(YEAR From ORDER_DATE) as A_YEAR, 
             Sum(ORDER_AMOUNT) as ORD_AMOUNT, 0 as PAY_AMOUNT
      From orders
      Group By USER_ID, Extract(YEAR From ORDER_DATE)
  UNION ALL
      Select USER_ID, Extract(YEAR From PAYMENT_DATE) as A_YEAR, 
             0 as ORD_AMOUNT, Sum(PAYMENT_AMOUNT) as PAY_AMOUNT
      From payments
      Group By USER_ID, Extract(YEAR From PAYMENT_DATE)
     )
GROUP BY USER_ID, A_YEAR
ORDER BY USER_ID, A_YEAR
/*
user_id a_year  ord_amount  pay_amount  balance
------- ------  ----------  ----------  -------
      1   2023       300.0          60    240.0
      1   2024         100           0      100
      2   2023         200          50      150
      2   2024          50           0       50
      3   2023           0          40      -40
      3   2024        1000           0     1000    */

And if you want to see all transactions with cumulative values partitioned by user and year (added total user balance too) then:

SELECT USER_ID,
       A_YEAR, 
       Coalesce(ORDER_DATE, PAYMENT_DATE) as A_DATE,
       ORD_AMOUNT, 
       Sum(ORD_AMOUNT) Over(Partition By USER_ID, A_YEAR 
                            Order By Coalesce(ORDER_DATE, PAYMENT_DATE)
                            Rows Between Unbounded Preceding And Current Row) as CUMUL_ORD_AMT,
       PAY_AMOUNT, 
       Sum(PAY_AMOUNT) Over(Partition By USER_ID, A_YEAR 
                            Order By Coalesce(ORDER_DATE, PAYMENT_DATE)
                            Rows Between Unbounded Preceding And Current Row) as CUMUL_PAY_AMT,
       Sum(ORD_AMOUNT) Over(Partition By USER_ID, A_YEAR 
                            Order By Coalesce(ORDER_DATE, PAYMENT_DATE)
                            Rows Between Unbounded Preceding And Current Row) -
       Sum(PAY_AMOUNT) Over(Partition By USER_ID, A_YEAR 
                            Order By Coalesce(ORDER_DATE, PAYMENT_DATE)
                            Rows Between Unbounded Preceding And Current Row) as BALANCE, 
       Sum(ORD_AMOUNT) Over(Partition By USER_ID 
                            Order By Coalesce(ORDER_DATE, PAYMENT_DATE)
                            Rows Between Unbounded Preceding And Current Row) -
       Sum(PAY_AMOUNT) Over(Partition By USER_ID 
                            Order By Coalesce(ORDER_DATE, PAYMENT_DATE)
                            Rows Between Unbounded Preceding And Current Row) as TOTAL_USER_BALANCE
FROM ( Select USER_ID, ORDER_DATE, Null as PAYMENT_DATE,
       Extract(YEAR From ORDER_DATE) as A_YEAR, 
       ORDER_AMOUNT as ORD_AMOUNT, 0 as PAY_AMOUNT
      From orders
  UNION ALL
      Select USER_ID, Null as ORDER_DATE, PAYMENT_DATE, 
             Extract(YEAR From PAYMENT_DATE) as A_YEAR, 
             0 as ORD_AMOUNT, PAYMENT_AMOUNT as PAY_AMOUNT
      From payments
     )
ORDER BY USER_ID, Coalesce(ORDER_DATE, PAYMENT_DATE)

/*    R e s u l t :
user_id a_year  a_date      ord_amount  cumul_ord_amt   pay_amount  cumul_pay_amt   balance  total_user_balance
------- ------  ----------  ----------  -------------   ----------  -------------   -------  ------------------
      1   2023  2023-07-15       105.5          105.5            0              0     105.5               105.5
      1   2023  2023-11-15       194.5          300.0            0              0     300.0                 300
      1   2023  2023-12-01           0          300.0           60             60     240.0                 240
      1   2024  2024-01-15         100            100            0              0       100                 340
      2   2023  2023-08-10         100            100            0              0       100                 100
      2   2023  2023-09-10         100            200            0              0       200                 200
      2   2023  2023-12-02           0            200           50             50       150                 150
      2   2024  2024-10-10          50             50            0              0        50                 200
      3   2023  2023-12-03           0              0           40             40       -40                 -40
      3   2024  2024-01-20        1000           1000            0              0      1000                 960  */

There are several ways to perform the desired aggregations. The following demonstrates an approach using FULL JOIN without subqueries. The CTEs, orders and payments, are proxies for the actual tables. Note that the user/customer details have been left out of orders. This has been done because those details should be stored in separate tables that are referenced in orders so that the relations are in third-normal form instead of replicating the data across multiple rows.

WITH
  orders (id, user_id, amount, create_date, status_change_date) AS (
    VALUES (1, 1, 100, '2021-01-01'::date, '2021-01-01'::date),
           (2, 1, 200, '2023-01-01'::date, '2023-01-31'::date)
  ),
  payments (id, user_id, amount, create_date) AS (
    VALUES (1, 1, 50, '2022-01-01'::date),
           (2, 1, 25, '2022-02-01'::date),
           (3, 1, 10, '2023-06-01'::date)
  )
SELECT
  COALESCE(o.user_id, p.user_id) AS combined_user_id,
  EXTRACT(YEAR FROM COALESCE(o.status_change_date, p.create_date)) AS summary_year,
  SUM(COALESCE(o.amount, 0)) AS order_amount,
  SUM(COALESCE(p.amount, 0)) AS payment_amount,
  SUM(COALESCE(p.amount, 0) - COALESCE(o.amount, 0)) AS balance,
  MAX(o.status_change_date) AS latest_order_date,
  MAX(p.create_date) AS latest_payment_date
FROM
  orders o
  FULL JOIN payments p ON o.user_id = p.user_id
  AND DATE_TRUNC('year', o.status_change_date) = DATE_TRUNC('year', p.create_date)
GROUP BY
  combined_user_id, summary_year
ORDER BY
  combined_user_id, summary_year;

Running the above produces the following output:

combined_user_id	summary_year	order_amount	payment_amount	balance	latest_order_date	latest_payment_date
1	2021	100	0	-100	2021-01-01
1	2022	0	75	75	2022-02-01
1	2023	200	10	-190	2023-01-31	2023-06-01

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Selecting data with full join skips some rows

Answers