ads Table:
-one row per ad per day
date | ad_id | account_id | spend
2018-05-01 123 1101 100
2018-05-02 123 1101 125
2018-05-03 124 1101 150
2018-05-04 124 1101 150
2018-05-04 125 1105 150
2018-05-04 126 1105 150
2018-05-04 123 1101 150
2018-01-01 123 1101 150
I am trying to create a histogram to show the how much advertisers have spent in last 7 days. I want the first bucket to be $10-999.99 and others to be $1000-1999.99,$2000-2999.99 etc but this I want to achieve through automation not by manually mentioning buckets through case function.
My current code does well in creating even automated buckets:
select CONCAT(1000*FLOOR(last_7_days_spend/1000), "-", 1000*FLOOR(last_7_days_spend/1000)+999.99) "spend($)" , count(*) "frequency"
from
(select account_id, sum(spend) "last_7_days_spend"
from fb_ads
where date between date_sub(curdate(), interval 7 day) and date_sub(curdate(), interval 1 day)
group by account_id) as abc
group by 1
order by 1;
and it returns this:
spend | frequency
0-999.99 2
2000-2999.99 1
But want to write some similar kind of query which should filter out records and start from $10-999.99 instead of $0.00-999.99. Desired output:
spend | frequency
10-999.99 2
2000-2999.99 1
You'll need to use a CASE expression to define the first bucket, but you can automate the other buckets within that expression. Note that if you don't want a bucket for a spend of less than $10, you'll need to filter those values out:
SELECT
CASE WHEN last_7_days_spend < 1000 THEN '10-999.99'
ELSE CONCAT(1000*FLOOR(last_7_days_spend/1000), "-", 1000*FLOOR(last_7_days_spend/1000)+999.99)
END AS `spend($)`,
COUNT(*) AS `frequency`
FROM (
SELECT account_id, SUM(spend) AS `last_7_days_spend`
FROM fb_ads
WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY account_id
) as abc
WHERE last_7_days_spend >= 10
GROUP BY 1
ORDER BY 1
Small demo on db-fiddle