Building Summaries With Aggregates

Goal

You learn to fold raw rows into meaningful summaries using GROUP BY, HAVING, FILTER, and date_trunc.

Why it matters

Aggregation is the area of SQL that is used most often and goes wrong most silently. This is because no error occurs and only the numbers become strange.

Three things deserve special care. First, aggregate functions ignore NULL, so the denominator of an average may differ from what you expect. Second, in this data, return slips are mixed in as negative quantities, so if you add them up as they are, the sales volume decreases. Third, when you truncate a column that includes a time zone to the month, if you do not state the basis time zone, the answer varies with the server settings.

If you memorize the processing order, most of the confusion is sorted out. The order is FROM, WHERE, GROUP BY, aggregation, HAVING, SELECT, ORDER BY. The single fact that WHERE is before folding and HAVING is after folding explains "why can't I use count in WHERE".

Steps

1. Create a view containing the order count by status, named v_status_count. The columns are status, order_count.
2. Considering only orders whose status is paid, shipped, or delivered, create a view containing the revenue total by channel, named v_channel_revenue. The columns are channel, revenue.
3. Create a view containing only customers with 5 or more orders, named v_big_customers. The columns are customer_id, order_count.
4. Create a view containing, for each channel, the paid count and the cancelled count at once, named v_status_split. The columns are channel, paid_count, cancelled_count, and every channel must remain.
5. Create a view containing the monthly revenue of completed orders, named v_monthly_revenue. The columns are month (date type) and revenue, and when you truncate to the month, use ordered_at AT TIME ZONE 'Asia/Seoul' as the basis.
6. Create a view containing the average price by category rounded to no decimal places, named v_category_avg. The columns are category, avg_price.
7. Create a view containing the top 10 products by quantity sold, named v_top_products. The columns are product_id, name, sold_qty, and sum only rows with a positive quantity; sort by quantity descending and, when equal, by product_id ascending.
8. Create a view containing the customer count, revenue, and revenue per customer by tier, named v_tier_arpu. The columns are tier, customer_count, revenue, arpu; revenue counts only orders in a completed status, and customers with no orders are also included in the denominator. Round arpu to two decimal places.

Notes

• Processing order: FROM → WHERE → GROUP BY → aggregation → HAVING → SELECT → ORDER BY
• You use the count(*) FILTER (WHERE 조건) syntax in step 4 (the Korean word means "condition").
• Common mistake 1: in step 7, if you add up negative quantities as they are, the ranking changes.
• Common mistake 2: in step 8, you must remove duplicates so that customers are not counted several times because of the join.

Order count by status

Create a view containing the order count by status, named v_status_count. The columns are status, order_count.

Decide what to fold by, and use the aggregate function that counts rows.

Revenue total by channel

Considering only orders whose status is paid, shipped, or delivered, create a view containing the revenue total by channel, named v_channel_revenue. The columns are channel, revenue.

Cancelled, refunded, and pending orders must be excluded from revenue. Think about which clause filters before folding.

Keep only customers with many orders

Create a view containing only customers with 5 or more orders, named v_big_customers. The columns are customer_id, order_count.

You cannot put a condition on an aggregation result in WHERE. There is a separate clause that filters after folding.

Count two things in one scan

Create a view containing, for each channel, the paid count and the cancelled count at once, named v_status_split. The columns are channel, paid_count, cancelled_count, and every channel must remain.

There is a clause that lets you attach a condition after an aggregate function. Every channel must remain.

Monthly revenue aggregation

Create a view containing the monthly revenue of completed orders, named v_monthly_revenue. The columns are month (date type) and revenue, and when you truncate to the month, use ordered_at AT TIME ZONE 'Asia/Seoul' as the basis.

When truncating a column that includes a time zone, you must state the basis time zone so that you get the same answer wherever you run it.

Average price by category

Create a view containing the average price by category rounded to no decimal places, named v_category_avg. The columns are category, avg_price.

Use the function that rounds to no decimal places. If you do not specify the number of digits, it rounds to an integer.

Top 10 products by sales volume

Create a view containing the top 10 products by quantity sold, named v_top_products. The columns are product_id, name, sold_qty, and sum only rows with a positive quantity; sort by quantity descending and, when equal, by product_id ascending.

In this data, returns are in as negative quantities. If you add them up as they are, the ranking flips.

Revenue per customer by tier

Create a view containing the customer count, revenue, and revenue per customer by tier, named v_tier_arpu. The columns are tier, customer_count, revenue, arpu; revenue counts only orders in a completed status, and customers with no orders are also included in the denominator. Round arpu to two decimal places.

Customers with no orders must also go into the denominator. Make sure customers are not counted several times because of the join.