Percentage Difference Using CASE WHEN clause

user_15498879 0
Viewed 78

The table I am working with is called 'transactions'. The columns are id (customer id), amount (amount spent by customer), timestamp (time of purchase).

I am trying to query:

  • yesterdays revenue: sum of amount.
  • percent difference from 8 day's ago revenue to yesterday's revenue.
  • MTD.
  • percent difference from last months MTD to this months MTD.

SAMPLE DATA

id amount timestamp
1 50 2021-12-01
2 60 2021-12-02
3 70 2021-11-05
4 80 2022-01-26
5 90 2022-01-25
6 20 2022-01-26
7 80 2022-01-19

EXPECTED OUTPUT

yesterday_revenue pct_change_week_ago mtd pct_change_month_prior
100 0.25 270 0.50

This is my code. The percent change columns are both incorrect. Please help.

select

-- yesterday
sum(case when timestamp::date = current_date - 1 then amount else null end) yesterday_revenue,

-- yesterday v. last week
(sum(case when timestamp::date > current_date - 1 then amount else null end) - sum(case when timestamp::date = current_date - 8 then amount else null end))
/ sum(case when timestamp::date = current_date - 8 then amount else null end) pct_change_week_ago,

-- mtd
sum(case when date_trunc('month',timestamp) = date_trunc('month',CURRENT_DATE -1) then amount else null end) mtd,

-- mtd v. month prior
(sum(case when date_trunc('month',timestamp) = date_trunc('month',CURRENT_DATE -1) then amount else null end) - sum(case when date_trunc('month',timestamp) = date_trunc('month',CURRENT_DATE -1) - interval '1 month'
      and date_part('day',timestamp ) <= date_part('day', CURRENT_DATE -1) then amount else null end))
    / sum(case when date_trunc('month',timestamp) = date_trunc('month',CURRENT_DATE -1) - interval '1 month'
      and date_part('day',timestamp ) <= date_part('day', CURRENT_DATE -1) then amount else null end) pct_change_month_prior

from transactions
case postgresql sql
2 Answers

Some things to consider:

  • "yesterday vs last week" currently uses timestamp::date > current_date - 1 at the start. This will include transactions from today only, not yesterday (it says "greater than yesterday"). I think it should be timestamp::date = current_date - 1
  • I could be wrong here, but I think sum(case when date_trunc('month',timestamp) = date_trunc('month',CURRENT_DATE -1) will capture transactions on the current date as well if the current date is in the same month as yesterday. You may not want that.
  • As far as I can tell, 'pct_change_month_prior' should be 1.45, not 0.5. You have 110 in December and 270 in January. 270 - 110 = 160 and 160 / 110 = 1.45. Your existing query already returns that result. FWIW, you can also use new/old-1 to get the same result in a slightly simpler way.
user_15719500

OK, so really, it's about your maths in the SELECT part of your statement.

Amount changed is: new - old

As a multiplicative amount, it is (new - old) / old or new / old - 1

As a percentage, you need to multiply by 100... 100 * (new / old - 1) but I understand you aren't worried about this.

Further to this, let's make sure your new and old are correct.

Yesterday's sum:

sum(case when timestamp::date = CURRENT_DATE - 1 then amount else null end)

8 days ago sum:

sum(case when timestamp::date = CURRENT_DATE - 8 then amount else null end)

1 month to yesterday sum:

sum(case when timestamp::date > CURRENT_DATE - 1 - INTERVAL '1 month' AND timestamp::date <= CURRENT_DATE - 1 then amount else null end)

1 month to 1 month and 1 day ago sum:

sum(case when timestamp::date > CURRENT_DATE - 1 - INTERVAL '2 month' AND timestamp::date <= CURRENT_DATE - 1 - INTERVAL '1 month' then amount else null end)

Start of month to yesterday sum:

sum(case when timestamp::date > DATE_TRUNC('month', CURRENT_DATE - 1) AND timestamp::date <= CURRENT_DATE - 1 then amount else null end)

Start of yesterday's last month to a month ago yesterday sum:

sum(case when timestamp::date > DATE_TRUNC('month', CURRENT_DATE - 1 - INTERVAL '1 month') AND timestamp::date <= CURRENT_DATE - 1 - INTERVAL '1 month' then amount else null end)

It's important you don't change = to > or cropping to just the date part else you will include more than you really want.

Effectively, by cropping to the month part, it would almost always sum all transactions for two months.

user_12580340
Related