SELECT
DATE_TRUNC('day', payment.paid_at) AS "DAILY",
COUNT(CASE WHEN product.type = 'A' THEN 1 ELSE NULL END) AS "A count",
SUM(CASE WHEN product.type = 'A' AND payment.price_currency_code='USD' THEN payment.price_amount*1080 ELSE payment.price_amount END) AS "A revenue",
COUNT(CASE WHEN product.type = 'B' THEN 1 ELSE NULL END) AS "B count",
SUM(CASE WHEN product.type = 'B' AND payment.price_currency_code='USD' THEN payment.price_amount*1080 ELSE payment.price_amount END) AS "B revenue"
Hi,
I have a question when using "CASE WHEN" I've written the query as above but the outcome is inaccurate. In fact, the sum of revenue per each category calculates almost same amount when in reality it should be different (the count is correct though).
Would appreciate if you can point out what I'm missing or have done wrong!
Thanks :)