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

EXPECTED OUTPUT

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

CodePudding user response：

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.

CodePudding user response：

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.