Combining multiple queries

user_1432890 0
Viewed 31

I want a table with all customers and their last charge transaction date and their last invoice date. I have the first two, but don't know how to add the last invoice date to the table. Here's what I have so far:

WITH

--Last customer transaction 
cust_trans AS (
        SELECT customer_id, created
        FROM charges a
        WHERE created = (
           SELECT MAX(created) AS last_trans
           FROM charges b
           WHERE a.customer_id = b.customer_id)),

--All customers
all_cust AS (
        SELECT customers.id AS customer, customers.email, CAST(customers.created AS DATE) AS join_date, ((1.0 * customers.account_balance)/100) AS balance
    FROM customers),
    
--Last customer invoice    
cust_inv AS (
        SELECT customer_id, date
        FROM invoices a
        WHERE date = (
       SELECT MAX(date) AS last_inv
           FROM invoices b
           WHERE a.customer_id = b.customer_id))
        
SELECT * FROM cust_trans
RIGHT JOIN all_cust ON all_cust.customer = cust_trans.customer_id
ORDER BY join_date;
sql
1 Answers

This should get what you need. Notice each individual subquery is left-joined to the customer table, so you always START with the customer, and IF there is a corresponding record in each subquery for max charge date or max invoice date, it will be pulled in. Now, you may want to apply a COALESCE() for the max dates to prevent showing nulls, such as

COALESCE(maxCharges.LastChargeDate, '') AS LastChargeDate

but your call.

SELECT
    c.id AS customer, 
    c.email, 
    CAST(c.created AS DATE) AS join_date, 
    ((1.0 * c.account_balance) / 100) AS balance,
    maxCharges.LastChargeDate,
    maxInvoices.LastInvoiceDate
FROM
    customers c
LEFT JOIN
    (SELECT
         customer_id,
         MAX(created) LastChargeDate
     FROM
         charges
     GROUP BY
         customer_id) maxCharges ON c.id = maxCharges.customer_id
LEFT JOIN
    (SELECT
         customer_id,
         MAX(date) LastInvoiceDate
     FROM
         invoices
     GROUP BY
         customer_id) maxInvoices ON c.id = maxInvoices.customer_id
ORDER BY
    c.created
user_741950
Related