Window Functions, CTEs, and Range Joins

Thu Apr 09 2026 15:45:26 GMT+0000 (Coordinated Universal Time)

Saved by @yasvanthM

WITH RankedSales AS (
    SELECT 
        category_id, 
        product_name, 
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category_id 
            ORDER BY revenue DESC, product_name ASC
        ) as rank_id
    FROM SALES
)
SELECT category_id, product_name, revenue
FROM RankedSales
WHERE rank_id <= 2;

---
  
  SELECT DISTINCT a.user_id
FROM USER_LOGINS a
JOIN USER_LOGINS b 
  ON a.user_id = b.user_id 
  AND a.login_date = b.login_date - INTERVAL '1 day'
ORDER BY a.user_id;

---
  
  SELECT 
    e.name, 
    COALESCE(b.bonus_amount, 0) AS final_bonus
FROM EMPLOYEES e
LEFT JOIN BONUSES b ON e.emp_id = b.emp_id
ORDER BY final_bonus DESC;


---
  
  SELECT MAX(tour_count) AS max_eligible_tours
FROM (
    SELECT 
        f.ID, 
        COUNT(c.ID) AS tour_count
    FROM FAMILIES f
    LEFT JOIN COUNTRIES c 
        ON f.FAMILY_SIZE BETWEEN c.MIN_SIZE AND c.MAX_SIZE
    GROUP BY f.ID
) AS family_eligibility;

1. The "Top-N" Product Performance (Window Functions) Scenario: You have a SALES table (sale_id, category_id, product_name, revenue). Task: Identify the top 2 highest-grossing products in each category. If there is a tie in revenue, use the product name (alphabetical) as the tie-breaker. Senior Tip: Using ROW_NUMBER() ensures exactly 2 rows per category; RANK() might return more if there are identical revenues. --- 2. Active User "Churn" Detection (Self-Joins & Dates) Scenario: A USER_LOGINS table (user_id, login_date). Task: Find "Retained Users"—defined as users who logged in on two consecutive days. Output the list of unique user_ids. Senior Tip: For IBM DB2 (often used internally), the syntax might be b.login_date - 1 DAY. This tests your ability to handle non-equi joins. --- 3. Handling Missing Data (COALESCE & Aggregations) Scenario: Two tables: EMPLOYEES (emp_id, name) and BONUSES (emp_id, bonus_amount). Not every employee received a bonus. Task: List all employees and their bonus amounts. If an employee received no bonus, display 0. Sort by bonus amount descending. Senior Tip: This tests the Left Join vs. Inner Join distinction. An Inner Join would incorrectly delete employees who didn't get a bonus. ---- The Scenario Table FAMILIES: ID, NAME, FAMILY_SIZE Table COUNTRIES: ID, NAME, MIN_SIZE, MAX_SIZE The Goal: Find the maximum number of tours a single family is eligible for. A family is eligible if their FAMILY_SIZE falls between a country's MIN_SIZE and MAX_SIZE (inclusive). The Solution To solve this, you first calculate the count of eligible countries for every family, and then take the MAX of those counts.