Invoice Analysis

group_by_user_and_month.sql

SELECT user_id, to_char(date, 'YYYY-MM') AS month, SUM(amount) as total 
FROM invoices
GROUP BY user_id, month
ORDER BY user_id, month;

grouping_by_month.sql

SELECT invoices.user_id, 
    to_char(invoices.date, 'YYYY-MM') AS invoices_month, 
    SUM(invoices.amount) AS invoices_amount,
    SUM(last_month_invoices.amount) AS last_month_amount,
    SUM(past_months_invoices.amount) AS past_months_amount
FROM invoices 
LEFT JOIN invoices as last_month_invoices
    ON last_month_invoices.user_id = invoices.user_id
        AND last_month_invoices.date >= 
            date_trunc('month', invoices.date - '1 month'::interval)::date
        AND last_month_invoices.date <=
            date_trunc('month', invoices.date)::date - 1
LEFT JOIN invoices as past_months_invoices
    ON past_months_invoices.user_id = invoices.user_id
        AND past_months_invoices.date <=
            date_trunc('month', invoices.date - '1 month'::interval)::date - 1
GROUP BY invoices.user_id, invoices_month
ORDER BY invoices_month, invoices.user_id

schema.sql

CREATE TABLE invoices(
  user_id INTEGER, date DATE, amount INTEGER
);