94. JOIN + Aggregate Functions =

If you thought that we’re done with grouping and counting forever – nope, that was only the beginning The real power of this technique comes when we’re joining different tables and then grouping the records in the joined table.

Let’s look at the example of a query we wrote to check revenues per subscription. It’s time to improve it

SELECT CASE WHEN product_id = 2 THEN 'monthly' WHEN product_id = 3 THEN 'yearly' ELSE 'other_plan' END AS plan, SUM(amount) AS revenue FROM purchases WHERE refunded = FALSE GROUP BY 1 

The main problem we identified...