SQL query to return total and percentage calculation grouped by day

Question

I have the following table in my database:

  id   | amount_cents |          date       | category 
-------+--------------+---------------------+---------------
 45714 |         -799 | 2024-10-01 00:00:00 | indifferent   
 27638 |        -6500 | 2024-10-01 00:00:00 | bad           
 27636 |        -6504 | 2024-10-01 00:00:00 | good          
 40488 |         -532 | 2024-10-01 00:00:00 | bad           
 40788 |       -89300 | 2024-10-02 00:00:00 | good          
 40789 |       -93830 | 2024-10-02 00:00:00 | indifferent   
 50087 |        -5748 | 2024-10-02 00:00:00 | bad           
 49360 |         -775 | 2024-10-02 00:00:00 | good          
 22337 |       -16000 | 2023-10-02 00:00:00 | good    

I'm trying to return a data set similar to the following, where the total amounts and percentages for each category are calculated for each day:

     date_trunc      | good_count | good_amount_cents | good_percentage_count | good_percentage_amount | bad_count | bad_amount_cents | bad_percentage_count | bad_percentage_amount | indifferent_count | indifferent_amount_cents | indifferent_percentage_count | indifferent_percentage_amount 
---------------------+------------+-------------------+-----------------------+------------------------+-----------+------------------+----------------------+-----------------------+-------------------+--------------------------+------------------------------+-------------------------------
 2023-10-01 00:00:00 |          1 |             -6504 |                 0.250 |                  0.453 |         2 |            -7032 |                0.500 |                 0.490 |                 1 |                     -799 |                        0.500 |                         0.055
 2023-10-02 00:00:00 |          3 |           -106075 |                 0.600 |                  0.515 |         1 |            -5748 |                0.200 |                 0.027 |                 1 |                   -93830 |                        0.200 |                         0.456

So far I've got as far as getting the total count of transactions grouped by date:

SELECT DATE_TRUNC('day', date), count(id) FROM transactions WHERE category IS NOT NULL GROUP BY DATE_TRUNC('day', date);

     date_trunc      | count 
---------------------+-------
 2024-10-01 00:00:00 |     8
 2024-10-02 00:00:00 |    19

How do I extend that to then (a) group by the various category, (b) sum the amounts_cent for each category and then calculate the percentage_count and percentage_amount for each category in each date?

Answer 1

We can use conditional aggregation here:

SELECT
    "date"::date,
    COUNT(*) FILTER (WHERE category = 'good') AS good_count,
    SUM(amount_cents) FILTER (WHERE category = 'good') AS good_amount_cents,
    1.0 * COUNT(*) FILTER (WHERE category = 'good') / COUNT(*) AS good_percentage_count,
    1.0 * SUM(amount_cents) FILTER (WHERE category = 'good') / SUM(amount_cents) AS good_percentage_amount,
    COUNT(*) FILTER (WHERE category = 'bad') AS bad_count,
    SUM(amount_cents) FILTER (WHERE category = 'bad') AS bad_amount_cents,
    1.0 * COUNT(*) FILTER (WHERE category = 'bad') / COUNT(*) AS bad_percentage_count,
    1.0 * SUM(amount_cents) FILTER (WHERE category = 'bad') / SUM(amount_cents) AS bad_percentage_amount,
    COUNT(*) FILTER (WHERE category = 'indifferent') AS indifferent_count,
    SUM(amount_cents) FILTER (WHERE category = 'indifferent') AS indifferent_amount_cents,
    1.0 * COUNT(*) FILTER (WHERE category = 'indifferent') / COUNT(*) AS indifferent_percentage_count,
    1.0 * SUM(amount_cents) FILTER (WHERE category = 'indifferent') / SUM(amount_cents) AS indifferent_percentage_amount
FROM transactions
GROUP BY "date"::date
ORDER BY "date"::date;