SQL: Find out the expense that each customer has made through card and cash

I have three tables:

transactions
customers
credit card

Here, If you look at the transaction table entries, entries with the same tran_id are for transactions done by the same customer. Tran_seq_nbr gives the sequence of transactions done by that customer.

I want to find out the expense that each customer has made through card and cash and result will look like this:

Solution Example

I tried the query as:

    SELECT c.Cust_id, c.Cust_name, t.Card_no, IFNULL(t.card_type,'Cash_Payment') card_type, sum(t.Tran_sold_amt)
    FROM transactions t
    LEFT JOIN cards cd
    ON t.card_no = cd.card_no
    LEFT JOIN customers c
    ON c.cust_id = cd.cust_id

I’m not able to link card no back to cust_id as my query doen’t show desired details for record: John Doe name as card no is 125.

Please advice. Thanks in advance.

Answer

This is an odd data model. You have to go through the credit card to get the customer, but some transactions are partially in cash and partially by credit card.

In any case, you can impute a credit card using window functions and join. And then aggregate:

select cu.Cust_id, cu.Cust_name, t.Card_no,
       coalesce(t.card_type, 'Cash_Payment') as card_type,
       sum(t.Tran_sold_amt)
from (select t.*,
             max(t.Card_no) over (partition by Tran_id) as imputed_card_no
      from transactions t
     ) t left join
     cards ca
     on t.imputed_card_no = ca.card_no left join
     customers cu
     on cu.cust_id = ca.cust_id
group by cu.Cust_id, cu.Cust_name, t.Card_no,
       coalesce(t.card_type, 'Cash_Payment');

Note: This returns NULL for the card_no for cash payments. That makes sense to me. If you really want the imputed card number, you can use:

`coalesce(t.card_no, imputed_card_no)`