Practice & Assessment
Test your understanding of Aggregations and Grouping
Multiple Choice Questions
5A table has 5 orders. Two have a NULL discount. SELECT AVG(discount) returns what?
Which query correctly returns only customers who placed more than 3 orders?
Three rows have scores: 95, 95, 80. What does DENSE_RANK() return for the row with score 80?
You need total revenue and order count for each category, but only for orders placed after 2024-01-01, and only showing categories with revenue above 50000. Where do the two filters go?
What is the key difference between a window function using OVER() and GROUP BY with an aggregate?
Coding Challenges
1Sales Report Aggregation
Given an orders table with columns id, customer_id, category (VARCHAR), amount (NUMERIC), status (VARCHAR: 'completed'|'refunded'|'pending'), created_at (TIMESTAMP): Write a single query that returns for each category — total completed revenue (SUM of amount where status='completed'), average order value, number of unique customers, and number of total orders — but only for categories with more than 10 completed orders and total revenue above 5000. Sort by total revenue descending. Expected output: result set with columns category, total_revenue, avg_order_value, unique_customers, total_orders. Time estimate: 25 minutes.
Mini Project
Monthly Business Intelligence Report
Using an orders table (id, customer_id, amount, category, status, created_at), write a suite of five analytical queries: (1) Monthly revenue for the last 12 months using DATE_TRUNC, including month-over-month change using LAG. (2) Top 10 customers by lifetime value using GROUP BY and SUM. (3) Per-category order count and revenue, filtered to active categories (more than 5 orders), sorted by revenue. (4) Running total of daily revenue using a window function with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. (5) Customer ranking by order count within each category using DENSE_RANK and PARTITION BY. Each query must include a comment explaining the business question it answers.