Practice Exercises

Reading these queries is not the same as writing them. Open a SQL editor and write each one from scratch. If you get stuck, re-read the specific lesson — do not copy-paste solutions.

Table Schema for Exercises

Basic Aggregate Exercises

1. Find the total number of orders, total revenue, and average order value from the orders table
2. Count how many orders have a coupon_code and how many do not (use COUNT with and without column name)
3. Find the cheapest and most expensive product in the products table
4. Show total revenue and order count per order status (GROUP BY status)
5. Find customers who have placed more than 10 orders (GROUP BY + HAVING)
6. Find duplicate emails in the customers table using GROUP BY + HAVING COUNT(*) > 1

Window Function Exercises

1. Rank all orders by total_amount in descending order using ROW_NUMBER, RANK, and DENSE_RANK side by side
2. Find the most recent order per customer using ROW_NUMBER + PARTITION BY
3. Calculate a running total of daily revenue ordered by date
4. For each order, show the previous order amount for the same customer using LAG
5. Calculate each product's percentage of its category's total revenue using SUM with PARTITION BY
6. Find duplicate orders (same customer, same amount, same date) using ROW_NUMBER + PARTITION BY

Challenge Exercise

Write a single query that shows a daily dashboard: order_date, daily_orders, daily_revenue, 7-day moving average revenue, running total revenue, and the day-over-day percentage change in revenue. Use multiple window functions in one query.

-- Hint: Combine SUM, AVG, and LAG as window functions
SELECT
  order_date,
  COUNT(*) AS daily_orders,
  SUM(total_amount) AS daily_revenue,
  ROUND(AVG(SUM(total_amount)) OVER(
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS seven_day_avg,
  SUM(SUM(total_amount)) OVER(
    ORDER BY order_date
  ) AS cumulative_revenue,
  ROUND(
    (SUM(total_amount) - LAG(SUM(total_amount)) OVER(ORDER BY order_date))
    * 100.0 / NULLIF(LAG(SUM(total_amount)) OVER(ORDER BY order_date), 0),
    2
  ) AS pct_change
FROM orders
GROUP BY order_date
ORDER BY order_date;