Practice: top-N per group, duplicates and streaks

How to use this page

Connect with psql shop. Problems 2 and 3 need small extra tables; create them as shown (they are temporary and disappear when you disconnect).

1. Top-N per group. Show the most expensive product in each category.

   Show answer

   Number the products within each category by price, then keep number 1:

   SELECT category, name, price
   FROM (
     SELECT category, name, price,
            row_number() OVER (PARTITION BY category ORDER BY price DESC, id) AS rn
     FROM products
   ) t
   WHERE rn = 1
   ORDER BY category;

     category   |            name             |  price
   -------------+-----------------------------+----------
    Accessories | Mechanical keyboard         |  4500.00
    Audio       | Noise-cancelling headphones | 12999.00
    Displays    | 27-inch monitor             | 18999.00
    Furniture   | Office chair                | 14500.00
    Storage     | Portable SSD 1TB            |  7999.00
    Video       | Webcam                      |  3999.00

   Change rn = 1 to rn <= 2 for the top two. Use rank() instead if tied products should all be shown. id in the window’s ORDER BY makes ties repeatable.

   I could explain this without looking

2. Duplicates. A sign-up form submitted twice, and an email typed with a capital letter, left duplicate rows. Find every row that is not the first sign-up for its email, ignoring case.

   Show answer

   CREATE TEMP TABLE signups (id int, email text, signed_up_at timestamptz);
   INSERT INTO signups VALUES
     (1, 'asha@example.com', '2025-01-12 09:00+00'), (2, 'asha@example.com', '2025-01-12 09:00:03+00'),
     (3, 'ben@example.com',  '2025-02-03 10:00+00'), (4, 'chen@example.com', '2025-02-20 11:00+00'),
     (5, 'chen@example.com', '2025-03-01 08:00+00'), (6, 'Chen@example.com', '2025-03-02 08:00+00');

   SELECT id, email, signed_up_at
   FROM (
     SELECT *, row_number() OVER (PARTITION BY lower(email) ORDER BY signed_up_at, id) AS rn
     FROM signups
   ) t
   WHERE rn > 1
   ORDER BY id;

   Rows 2, 5 and 6. Delete them with DELETE FROM signups WHERE id IN (…) using the same query. Then add a unique index on lower(email) so it cannot happen again.

   I could explain this without looking

3. Gaps and islands. From a table of the days each customer logged in, find each customer’s longest streak of consecutive days.

   Show answer

   CREATE TEMP TABLE logins (customer_id int, day date);
   INSERT INTO logins VALUES
     (1,'2025-09-01'),(1,'2025-09-02'),(1,'2025-09-03'),(1,'2025-09-05'),(1,'2025-09-06'),(1,'2025-09-10'),
     (2,'2025-09-01'),(2,'2025-09-03'),(2,'2025-09-04'),(2,'2025-09-05'),(2,'2025-09-06');

   The trick: subtract each row’s position from its date. Within a run of consecutive days, the date and the position both go up by one each row, so the difference stays the same. A new difference means a new run:

    customer_id |    day     |   island
   -------------+------------+------------
              1 | 2025-09-01 | 2025-08-31
              1 | 2025-09-02 | 2025-08-31
              1 | 2025-09-03 | 2025-08-31
              1 | 2025-09-05 | 2025-09-01
              1 | 2025-09-06 | 2025-09-01
              1 | 2025-09-10 | 2025-09-04

   Group by that difference to get the streaks:

   SELECT customer_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS days
   FROM (
     SELECT customer_id, day,
            day - row_number() OVER (PARTITION BY customer_id ORDER BY day)::int AS island
     FROM logins
   ) t
   GROUP BY customer_id, island
   ORDER BY customer_id, streak_start;

    customer_id | streak_start | streak_end | days
   -------------+--------------+------------+------
              1 | 2025-09-01   | 2025-09-03 |    3
              1 | 2025-09-05   | 2025-09-06 |    2
              1 | 2025-09-10   | 2025-09-10 |    1
              2 | 2025-09-01   | 2025-09-01 |    1
              2 | 2025-09-03   | 2025-09-06 |    4

   Longest streaks: 3 days for customer 1, 4 for customer 2. The same pattern finds runs of failed payments, outages, or consecutive sales days.

   I could explain this without looking

4. Share of the total. Each category’s revenue (non-cancelled orders) and its percentage of all revenue.

   Show answer

   Group first, then apply a window over the groups. Notice sum(sum(...)) OVER (): the inner sum is the per-category aggregate, and the outer sum … OVER () adds up those results across all categories.

   SELECT p.category,
          sum(oi.quantity * oi.unit_price) AS revenue,
          round(100 * sum(oi.quantity * oi.unit_price)
                / sum(sum(oi.quantity * oi.unit_price)) OVER (), 1) AS pct
   FROM order_items oi
   JOIN products p ON p.id = oi.product_id
   JOIN orders o   ON o.id = oi.order_id
   WHERE o.status <> 'cancelled'
   GROUP BY p.category
   ORDER BY revenue DESC;

     category   | revenue  | pct
   -------------+----------+------
    Displays    | 56997.00 | 39.6
    Furniture   | 33398.00 | 23.2
    Accessories | 25595.00 | 17.8
    Audio       | 11999.00 |  8.3
    Storage     |  7999.00 |  5.6
    Video       |  7998.00 |  5.6

   I could explain this without looking