SQL Practice Questions: 25 Real Business Queries & Answers

Landing a data analyst, analytics engineer, or business intelligence role requires more than reciting syntax definitions. Hiring managers evaluate your ability to translate ambiguous business requirements into resilient, optimized, production-grade queries.

In day-to-day analytics, you rarely run isolated queries on a single, cleanly formatted table. You navigate normalized schemas, handle messy customer contact records, diagnose join fan-out double-counting, and write modular analytical pipelines using Common Table Expressions (CTEs) and window functions.

This guide provides 25 real-world SQL practice questions organized across three progressive tiers:

1. Beginner SQL Practice Questions: Foundational filtering, text pattern matching, multi-column sorting, and aggregated groupings with HAVING.
2. Intermediate SQL Queries: Multi-table relational joins (INNER, LEFT, SELF), correlated subqueries, CASE WHEN conditional aggregations, and missing-data sanitization.
3. Advanced Analytical Queries: Analytical window functions (ROW_NUMBER, DENSE_RANK, LAG, LEAD, NTILE), recursive hierarchical CTEs, moving window frames, and the classic Gaps and Islands problem.

Every practice query includes a realistic Business Problem, production-ready PostgreSQL Solution, detailed Logic Breakdown, and Expected Output Table.

You can run these queries directly in our interactive sandbox at Topfolio SQL Basics Course. Every course and exercise on Topfolio is 100% free to learn, with an optional ₹99 verified certificate to showcase your validated query expertise on LinkedIn.

Relational Schema Architecture

All 25 practice questions query the unified retail and human resources relational schema below. Take a moment to understand table primary keys (PK), foreign key relationships (FK), and cardinality before writing queries.

+----------------------------------------------------------------------------------------------------+
|                                    ENTERPRISE RELATIONAL SCHEMA                                    |
+----------------------------------------------------------------------------------------------------+

    [ departments ]                            [ employees ]
    +-------------------------+                 +-------------------------+
    | department_id (PK)      |<----------------| department_id (FK)      |
    | department_name VARCHAR |                 | employee_id (PK)        |
    | budget NUMERIC          |                 | first_name VARCHAR      |
    +-------------------------+                 | last_name VARCHAR       |
                                                | salary NUMERIC          |
                                                | hire_date DATE          |
                                                | manager_id (FK)---------+--+ (Self-Referential)
                                                +-------------------------+  |
                                                             ^               |
                                                             +---------------+

    [ customers ]              [ orders ]                 [ order_items ]              [ products ]
    +--------------------+     +--------------------+     +---------------------+      +---------------------+
    | customer_id (PK)   |<----| customer_id (FK)   |<----| order_id (FK)       |      | product_id (PK)     |
    | first_name VARCHAR |     | order_id (PK)      |     | order_item_id (PK)  |----->| product_id (FK)     |
    | last_name VARCHAR  |     | order_date DATE    |     | quantity INT        |      | product_name VARCHAR|
    | email VARCHAR      |     | status VARCHAR     |     | unit_price NUMERIC  |      | category VARCHAR    |
    | city VARCHAR       |     | total_amount NUM   |     +---------------------+      | price NUMERIC       |
    | state CHAR(2)      |     +--------------------+                                  | stock_quantity INT  |
    | signup_date DATE   |                                                             +---------------------+
    +--------------------+

Schema Relationship Rules:

• One-to-Many (departments to employees): A department employs zero or more employees; each employee belongs to exactly one department.
• Self-Join (employees to employees): Every employee reports to a manager (manager_id), except the executive leader whose manager_id is NULL.
• One-to-Many (customers to orders): A customer can place zero or multiple orders over time.
• Many-to-Many (orders to products via order_items): An order contains multiple line items; each line item references a distinct product.

SQL Query Complexity Matrix

The matrix below maps the query tiers, core PostgreSQL clauses, analytical applications, and frequent interview failure modes covered in this guide.

Part 1: Beginner SQL Practice Questions

Beginner SQL practice tests your mastery of single-table data filtering, logical operator precedence, string searching, and grouped aggregations.

Question 1: Active High-Value Customer Filtering

Business Problem: The marketing team is launching a VIP promotional campaign in key geographic regions. Retrieve the first name, last name, email, and signup date of all customers who registered on or after January 1, 2024, reside in California (CA) or Texas (TX), ordered by signup date from newest to oldest.

-- Solution: Filtering by date and state membership
SELECT 
    first_name,
    last_name,
    email,
    state,
    signup_date
FROM customers
WHERE signup_date >= '2024-01-01'
  AND state IN ('CA', 'TX')
ORDER BY signup_date DESC;

Query Logic: The WHERE clause combines a temporal comparison with the IN operator for set membership. The ORDER BY signup_date DESC sorts records chronologically backwards so the latest signups appear first.

Expected Output:

Question 2: Product Search by Pattern and Price Boundaries

Business Problem: The product merchandising team needs a list of all items in inventory whose names contain the strings "Pro" or "Air" (case-insensitive) and whose unit price falls strictly between $50 and $500.

-- Solution: Pattern matching with ILIKE and BETWEEN
SELECT 
    product_id,
    product_name,
    category,
    price
FROM products
WHERE (product_name ILIKE '%Pro%' OR product_name ILIKE '%Air%')
  AND price BETWEEN 50.00 AND 500.00
ORDER BY price ASC;

Query Logic: In PostgreSQL, ILIKE performs case-insensitive regex pattern matching. Note the explicit parentheses grouping the OR conditions. Without parentheses, AND evaluates before OR, which would corrupt the price boundary filter.

Expected Output:

Question 3: Department Headcount and Compensation Summary

Business Problem: Human Resources requires a departmental breakdown of employee headcount and average compensation. Display the department ID, total employee headcount, and average salary (rounded to 2 decimal places) for all departments employing at least 3 individuals.

-- Solution: Aggregation with GROUP BY and HAVING
SELECT 
    department_id,
    COUNT(employee_id) AS total_employees,
    ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) >= 3
ORDER BY total_employees DESC;

Query Logic: GROUP BY department_id compresses individual employee rows into department buckets. To filter aggregated groups, you must use HAVING rather than WHERE, because WHERE filters rows before aggregation occurs. For a deep dive into execution order, read our guide on SQL GROUP BY vs HAVING.

Expected Output:

Question 4: Order Status and Transaction Thresholds

Business Problem: Finance needs to audit high-value completed or shipped transactions. Retrieve the order ID, customer ID, order date, status, and total amount for orders placed between February 1, 2024, and April 30, 2024, where the status is either 'completed' or 'shipped' and the total amount exceeds $250.00.

-- Solution: Multi-condition transactional filter
SELECT 
    order_id,
    customer_id,
    order_date,
    status,
    total_amount
FROM orders
WHERE order_date >= '2024-02-01'
  AND order_date <= '2024-04-30'
  AND status IN ('completed', 'shipped')
  AND total_amount > 250.00
ORDER BY total_amount DESC
LIMIT 5;

Query Logic: This query applies range-based date boundaries alongside categorical filtering. Sorting by total_amount DESC combined with LIMIT 5 produces the top 5 highest-value orders fulfilling the criteria.

Expected Output:

Question 5: Total Inventory Valuation by Category

Business Problem: Supply Chain Management needs to evaluate capital tied up in warehouse stock. Calculate the total inventory value (price * stock_quantity) for each product category. Show the category name, total distinct products, and total inventory value, sorted by total value descending.

-- Solution: Column arithmetic inside SUM aggregation
SELECT 
    category,
    COUNT(product_id) AS distinct_products,
    ROUND(SUM(price * stock_quantity), 2) AS total_inventory_value
FROM products
GROUP BY category
ORDER BY total_inventory_value DESC;

Query Logic: SQL allows row-level arithmetic (price * stock_quantity) inside the SUM() aggregate function. The database calculates the product of price and quantity for each row, then aggregates the sum across each category partition.

Expected Output:

Question 6: Underperforming Department Salary Cap Detection

Business Problem: Leadership is reviewing compensation equity across business units. Identify all departments where no employee earns more than $75,000 (i.e. the maximum salary within the department is strictly less than 75,000).

-- Solution: Group filtering on MAX aggregate
SELECT 
    department_id,
    COUNT(employee_id) AS employee_count,
    MAX(salary) AS highest_salary,
    ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) < 75000.00
ORDER BY highest_salary DESC;

Query Logic: This question tests whether you recognize that extreme-value boundary conditions can be tested using MAX() < threshold within the HAVING clause. Departments with any executive or senior compensation exceeding 75,000 are automatically excluded.

Expected Output:

Question 7: Monthly Unique Purchasing Customer Volume

Business Problem: Growth leads need to track customer engagement trends over time. Calculate the number of unique customers who placed an order during each calendar month of 2024.

-- Solution: DATE_TRUNC with COUNT DISTINCT
SELECT 
    DATE_TRUNC('month', order_date)::DATE AS sales_month,
    COUNT(order_id) AS total_orders,
    COUNT(DISTINCT customer_id) AS unique_purchasing_customers
FROM orders
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)::DATE
ORDER BY sales_month ASC;

Query Logic: DATE_TRUNC('month', order_date) truncates dates to the first day of their respective month. We use COUNT(DISTINCT customer_id) rather than COUNT(customer_id) because a single customer may place multiple orders in a single month; failing to use DISTINCT measures order volume rather than active customer reach.

Expected Output:

Question 8: Inventory Alert for Critical Stock or High-Margin Items

Business Problem: Warehouse supervisors need an operational alert list of products requiring immediate attention. Identify products where either the stock quantity has fallen below 15 units, or the unit price exceeds $400.00, while excluding any product whose category is 'Discontinued' or 'Archived'.

-- Solution: Complex boolean logic with exclusion
SELECT 
    product_id,
    product_name,
    category,
    price,
    stock_quantity
FROM products
WHERE (stock_quantity < 15 OR price > 400.00)
  AND category NOT IN ('Discontinued', 'Archived')
ORDER BY stock_quantity ASC, price DESC;

Query Logic: Demonstrates proper operator hierarchy. The parenthesis encapsulates the alternative alert trigger conditions (low stock OR high price), while the outer AND NOT IN prevents retired catalog items from triggering false alarms.

Expected Output:

Part 2: Intermediate SQL Queries

Intermediate queries reflect everyday business analytics: combining tables via relational keys, identifying missing or orphan records, transposing row states with conditional aggregations, and utilizing subqueries.

Question 9: Customer Lifetime Spend (INNER JOIN with Aggregation)

Business Problem: Customer Success needs a lifetime value (LTV) report. Return each customer's full name, email address, total completed orders placed, and cumulative spend across all completed orders. Display the top 5 highest-spending customers.

-- Solution: Joining customers and orders with aggregation
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    c.email,
    COUNT(o.order_id) AS completed_orders_count,
    ROUND(SUM(o.total_amount), 2) AS lifetime_spend
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
ORDER BY lifetime_spend DESC
LIMIT 5;

Query Logic: The INNER JOIN bridges customers to their corresponding transaction logs on customer_id. The string concatenation operator || merges first and last names. Notice that every column in the SELECT list that is not aggregated (customer_id, first_name, last_name, email) is included in the GROUP BY clause.

Expected Output:

Question 10: Inactive Customer Detection (LEFT JOIN / Anti-Join)

Business Problem: The CRM lifecycle marketing team is planning a reactivation campaign. Find all registered customers who have never placed an order in the database. Return their customer ID, full name, email, and registration date.

-- Solution: Anti-join via LEFT JOIN ... WHERE NULL
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    c.signup_date
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.signup_date ASC;

Query Logic: A LEFT JOIN preserves all records from the left table (customers) regardless of whether matching records exist in orders. For customers with zero orders, the joined columns from orders evaluate to NULL. The predicate WHERE o.order_id IS NULL isolates these non-purchasing accounts. This is a classic interview question testing the anti-join pattern.

Expected Output:

Question 11: Managerial Reporting Hierarchy (SELF JOIN)

Business Problem: Organizational Operations needs a company directory showing every employee alongside the full name of their direct manager. If an employee is the CEO or top executive without a manager, display 'Executive / None'.

-- Solution: Self-join with COALESCE fallback
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS employee_name,
    d.department_name,
    COALESCE(m.first_name || ' ' || m.last_name, 'Executive / None') AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_id
LEFT JOIN departments AS d ON e.department_id = d.department_id
ORDER BY e.department_id, e.employee_id;

Query Logic: A table can join to itself. Here, employees e represents the subordinate, while employees m represents the manager, linked by e.manager_id = m.employee_id. We use a LEFT JOIN so the top executive whose manager_id IS NULL is not discarded. COALESCE handles the null value with descriptive fallback text.

Expected Output:

Question 12: Transposing Order Status with Conditional Aggregation

Business Problem: Financial operations wants to pivot order values by status into columns. For each customer, display their customer ID, count of completed orders, and the dollar sum for completed orders, shipped orders, and cancelled orders.

-- Solution: Conditional aggregation with CASE WHEN
SELECT 
    customer_id,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders_count,
    ROUND(SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END), 2) AS completed_revenue,
    ROUND(SUM(CASE WHEN status = 'shipped' THEN total_amount ELSE 0 END), 2) AS in_transit_revenue,
    ROUND(SUM(CASE WHEN status = 'cancelled' THEN total_amount ELSE 0 END), 2) AS lost_cancelled_revenue
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) >= 5
ORDER BY completed_revenue DESC
LIMIT 5;

Query Logic: This pattern, known as conditional aggregation, simulates a pivot table directly in SQL. Inside SUM(), CASE WHEN status = 'completed' THEN total_amount ELSE 0 END evaluates the amount when the condition matches and adds zero otherwise.

Expected Output:

Question 13: Departmental Above-Average Earners (Correlated Subquery)

Business Problem: Total Rewards compensation analysts need to identify employees who earn more than the average salary of their respective department. Display the employee ID, full name, department ID, and salary.

-- Solution: Correlated subquery in WHERE
SELECT 
    e1.employee_id,
    e1.first_name || ' ' || e1.last_name AS employee_name,
    e1.department_id,
    e1.salary
FROM employees AS e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id, e1.salary DESC;

Query Logic: In a correlated subquery, the inner query references values from the outer query row (e2.department_id = e1.department_id). For every employee evaluated in e1, PostgreSQL calculates the dynamic average salary of their department and tests if e1.salary exceeds it.

Expected Output:

Question 14: Multi-Year Cohort Retention via EXISTS

Business Problem: The retention team wants to isolate persistent multi-year customers. Retrieve all customers who placed at least one order in calendar year 2023 AND placed at least one order in calendar year 2024.

-- Solution: Set intersection using paired EXISTS subqueries
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email
FROM customers AS c
WHERE EXISTS (
    SELECT 1 
    FROM orders AS o23
    WHERE o23.customer_id = c.customer_id
      AND o23.order_date >= '2023-01-01' 
      AND o23.order_date < '2024-01-01'
)
AND EXISTS (
    SELECT 1 
    FROM orders AS o24
    WHERE o24.customer_id = c.customer_id
      AND o24.order_date >= '2024-01-01' 
      AND o24.order_date < '2025-01-01'
)
ORDER BY c.customer_id ASC;

Query Logic: EXISTS checks for the presence of rows satisfying a condition and terminates evaluation as soon as the first match is found (short-circuit evaluation). Pairing two EXISTS blocks joined by AND guarantees that the customer completed transactions in both distinct years.

Expected Output:

Question 15: Missing Contact Sanitization with COALESCE and NULLIF

Business Problem: Customer support systems frequently receive dirty input. Clean the customer phone directory. If a customer has a mobile_phone, use it. If mobile_phone is null or empty string '', fallback to work_phone. If both are unavailable, output 'Phone Not Provided'.

-- Solution: Data cleaning using NULLIF and COALESCE
SELECT 
    customer_id,
    first_name || ' ' || last_name AS customer_name,
    COALESCE(
        NULLIF(TRIM(mobile_phone), ''), 
        NULLIF(TRIM(work_phone), ''), 
        'Phone Not Provided'
    ) AS primary_contact_phone
FROM customers
LIMIT 5;

Query Logic: NULLIF(val, '') converts whitespace or empty strings into true SQL NULLs. COALESCE scans values from left to right and returns the first non-null expression. This defensive pattern prevents whitespace strings from bypassing null checks.

Expected Output:

Question 16: Multi-Table Revenue Attribution without Join Fan-Out

Business Problem: E-commerce analysts need to calculate total gross sales value (quantity * unit_price) broken down by product category. Be careful not to double-count parent order amounts across multiple child order items.

-- Solution: Safe multi-table join on line-item grain
SELECT 
    p.category,
    COUNT(DISTINCT oi.order_id) AS total_orders_containing_category,
    SUM(oi.quantity) AS total_units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS gross_category_revenue
FROM products AS p
INNER JOIN order_items AS oi ON p.product_id = oi.product_id
INNER JOIN orders AS o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY gross_category_revenue DESC;

Query Logic: When joining orders, order_items, and products, line-level metrics must be computed at the child item grain (order_items). If you sum o.total_amount here, it would multiply across line items. For a complete analysis of this risk, read our comprehensive guide on SQL Join Fan-Out & Double Counting.

Expected Output:

Part 3: Advanced Analytical Queries

Advanced SQL queries distinguish senior analysts. These questions explore window functions, Common Table Expressions (CTEs), recursive trees, and temporal sequence algorithms.

Question 17: Top 2 Highest-Paid Employees per Department (DENSE_RANK)

Business Problem: Executive leadership wants to benchmark compensation tiers. Identify the top 2 highest-paid employees within each department. If employees tie for second place, include all tied individuals without skipping ranks.

-- Solution: Window ranking inside a Common Table Expression (CTE)
WITH ranked_employees AS (
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name AS employee_name,
        d.department_name,
        e.salary,
        DENSE_RANK() OVER (
            PARTITION BY e.department_id 
            ORDER BY e.salary DESC
        ) AS salary_rank
    FROM employees AS e
    INNER JOIN departments AS d ON e.department_id = d.department_id
)
SELECT 
    employee_id,
    employee_name,
    department_name,
    salary,
    salary_rank
FROM ranked_employees
WHERE salary_rank <= 2
ORDER BY department_name, salary_rank ASC;

Query Logic: In SQL logical query processing, window functions evaluate during the SELECT phase, which happens after WHERE. Consequently, you cannot filter window outputs directly in the same query block (e.g. WHERE DENSE_RANK() <= 2 throws a syntax error).

Wrapping the query in a CTE (WITH ranked_employees AS (...)) materializes the ranking, allowing the outer query to filter cleanly. We select DENSE_RANK() instead of ROW_NUMBER() or RANK() because it preserves ties without skipping subsequent rank integers. Compare ranking behaviors in our tutorial on SQL ROW_NUMBER, RANK, and DENSE_RANK and learn more CTE patterns in our SQL CTE Guide.

Expected Output:

Question 18: Month-over-Month (MoM) Revenue Growth Rate (LAG)

Business Problem: Financial planning and analysis (FP&A) requires a monthly revenue scorecard showing total monthly revenue, prior month revenue, and the month-over-month (MoM) percentage growth rate.

-- Solution: LAG window function across grouped monthly aggregates
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', order_date)::DATE AS sales_month,
        ROUND(SUM(total_amount), 2) AS current_month_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)::DATE
)
SELECT 
    sales_month,
    current_month_revenue,
    LAG(current_month_revenue, 1) OVER (ORDER BY sales_month ASC) AS prior_month_revenue,
    ROUND(
        (current_month_revenue - LAG(current_month_revenue, 1) OVER (ORDER BY sales_month ASC)) 
        / NULLIF(LAG(current_month_revenue, 1) OVER (ORDER BY sales_month ASC), 0) * 100.0, 
        2
    ) AS mom_growth_percentage
FROM monthly_revenue
ORDER BY sales_month ASC;

Query Logic: First, the CTE groups transactions into calendar months. Second, LAG(current_month_revenue, 1) OVER (ORDER BY sales_month ASC) accesses the revenue from the preceding month row without performing a self-join. Finally, NULLIF(..., 0) prevents division-by-zero runtime exceptions for baseline months.

Expected Output:

Question 19: Running Cumulative Cash Flow (SUM OVER)

Business Problem: The Treasury team tracks daily cumulative revenue to forecast cash runway. Calculate the daily transaction total and the cumulative running total revenue across all days in March 2024.

-- Solution: Cumulative window aggregation with explicit frame
WITH daily_revenue AS (
    SELECT 
        order_date,
        ROUND(SUM(total_amount), 2) AS daily_total
    FROM orders
    WHERE order_date BETWEEN '2024-03-01' AND '2024-03-05'
      AND status = 'completed'
    GROUP BY order_date
)
SELECT 
    order_date,
    daily_total,
    SUM(daily_total) OVER (
        ORDER BY order_date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_cumulative_revenue
FROM daily_revenue
ORDER BY order_date ASC;

Query Logic: The window specification ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instructs the engine to sum all rows from the beginning of the partition up through the active row. Specifying ROWS rather than RANGE provides superior query execution performance and avoids grouping peer timestamps together.

Expected Output:

Question 20: Purchase Velocity and Repeat Order Cadence (LEAD)

Business Problem: Customer Retention needs to know how many days elapse between repeat orders. For customers with multiple transactions, calculate the number of days between each order and their subsequent order.

-- Solution: LEAD window function with date subtraction
SELECT 
    customer_id,
    order_id,
    order_date AS current_order_date,
    LEAD(order_date, 1) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date ASC
    ) AS next_order_date,
    LEAD(order_date, 1) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date ASC
    ) - order_date AS days_until_next_purchase
FROM orders
WHERE status = 'completed'
ORDER BY customer_id, order_date ASC
LIMIT 6;

Query Logic: PARTITION BY customer_id resets the window boundary for every customer. LEAD(order_date, 1) pulls the timestamp from the subsequent row. In PostgreSQL, subtracting two DATE types yields an integer representing the elapsed number of days. If the customer has no subsequent order, LEAD returns NULL.

Expected Output:

Question 21: Customer Spend Quartile Segmentation (NTILE)

Business Problem: Marketing needs to divide all purchasing customers into 4 equal quartiles based on their lifetime spend. Quartile 1 represents the highest spenders (VIPs), while Quartile 4 represents low-tier purchasers.

-- Solution: NTILE statistical bucket distribution
WITH customer_spend AS (
    SELECT 
        customer_id,
        ROUND(SUM(total_amount), 2) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT 
    customer_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spend_quartile
FROM customer_spend
ORDER BY spend_quartile ASC, total_spent DESC;

Query Logic: NTILE(4) calculates the total row count of the partition and evenly distributes rows across 4 buckets numbered 1 through 4. Because the window is ordered by total_spent DESC, the top 25% of spenders receive bucket 1.

Expected Output:

Question 22: First-Order Baseline vs Repeat Spending (FIRST_VALUE)

Business Problem: Growth analysts want to determine if a customer's first purchase value correlates with their average lifetime order value (AOV). For each completed order, return the customer ID, order date, current order amount, and the value of their very first order.

-- Solution: FIRST_VALUE window function
SELECT 
    customer_id,
    order_id,
    order_date,
    total_amount,
    FIRST_VALUE(total_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS initial_acquisition_order_amount
FROM orders
WHERE status = 'completed'
ORDER BY customer_id, order_date ASC
LIMIT 5;

Query Logic: FIRST_VALUE(total_amount) extracts the initial order value for that customer partition. Expanding the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures that the function evaluates across the entire partition regardless of where the current cursor sits.

Expected Output:

Question 23: Recursive Organizational Hierarchy Mapping (WITH RECURSIVE)

Business Problem: Human Resources needs a full organizational chart depicting employee reporting depth levels. Start with executive leaders who report to no one (manager_id IS NULL) as Level 1, and recursively traverse down each management tier.

-- Solution: Recursive Common Table Expression
WITH RECURSIVE org_hierarchy AS (
    -- Anchor Member: Top-level executives with no manager
    SELECT 
        employee_id,
        first_name || ' ' || last_name AS employee_name,
        manager_id,
        1 AS management_level,
        first_name || ' ' || last_name AS reporting_path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- Recursive Member: Subordinates joining to prior level
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name AS employee_name,
        e.manager_id,
        oh.management_level + 1 AS management_level,
        oh.reporting_path || ' -> ' || (e.first_name || ' ' || e.last_name) AS reporting_path
    FROM employees AS e
    INNER JOIN org_hierarchy AS oh ON e.manager_id = oh.employee_id
)
SELECT 
    employee_id,
    employee_name,
    management_level,
    reporting_path
FROM org_hierarchy
ORDER BY management_level ASC, employee_id ASC;

Query Logic: Recursive CTEs contain two queries united by UNION ALL:

1. The Anchor Query: Initializes the base result set (where manager_id IS NULL).
2. The Recursive Query: Iteratively joins employees back to the growing results of org_hierarchy until no more matching subordinate rows are found. It tracks tier levels by incrementing management_level + 1.

Expected Output:

Question 24: Consecutive Activity Streaks (Gaps and Islands)

Business Problem: Product analytics wants to reward loyal daily users. Given daily platform login/order dates, identify consecutive purchase streaks for customer 1045. A streak consists of 2 or more consecutive days of order activity.

-- Solution: The classic Row-Number Difference Gaps & Islands pattern
WITH distinct_customer_dates AS (
    SELECT DISTINCT 
        customer_id,
        order_date
    FROM orders
    WHERE customer_id = 1045
),
grouped_streaks AS (
    SELECT 
        customer_id,
        order_date,
        order_date - (ROW_NUMBER() OVER (ORDER BY order_date ASC) * INTERVAL '1 day') AS streak_island_key
    FROM distinct_customer_dates
)
SELECT 
    customer_id,
    MIN(order_date) AS streak_start_date,
    MAX(order_date) AS streak_end_date,
    COUNT(*) AS consecutive_active_days
FROM grouped_streaks
GROUP BY customer_id, streak_island_key
HAVING COUNT(*) >= 2
ORDER BY streak_start_date ASC;

Query Logic: This demonstrates the industry-standard Gaps and Islands algorithm. When consecutive calendar dates are paired with consecutive row numbers (ROW_NUMBER()), subtracting (row_num * 1 day) from order_date produces a constant baseline date (streak_island_key) for every contiguous day in that streak. The moment a gap in dates occurs, the difference shifts to a new key value. Grouping by this key aggregates the streak length!

Expected Output:

Question 25: 3-Month Rolling Average Revenue per Category

Business Problem: Inventory and revenue planning teams must smooth seasonal sales spikes. Compute the 3-month rolling average monthly gross revenue for each product category (spanning the current month and preceding 2 months).

-- Solution: Moving window frame (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
WITH monthly_category_sales AS (
    SELECT 
        p.category,
        DATE_TRUNC('month', o.order_date)::DATE AS sales_month,
        ROUND(SUM(oi.quantity * oi.unit_price), 2) AS monthly_sales
    FROM products AS p
    INNER JOIN order_items AS oi ON p.product_id = oi.product_id
    INNER JOIN orders AS o ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY p.category, DATE_TRUNC('month', o.order_date)::DATE
)
SELECT 
    category,
    sales_month,
    monthly_sales,
    ROUND(
        AVG(monthly_sales) OVER (
            PARTITION BY category 
            ORDER BY sales_month ASC
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 
        2
    ) AS rolling_3_month_average_sales
FROM monthly_category_sales
ORDER BY category, sales_month ASC;

Query Logic: The clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW instructs AVG() to calculate the arithmetic mean of at most 3 data points (the two prior calendar months plus the active month). When evaluated on the first month in the series, it computes the single-month average; by month 3, it produces the full 3-month smoothed moving metric.

Expected Output:

5-Step Strategic Framework for Technical SQL Interviews

When faced with a live whiteboard or shared coding environment, top-performing candidates never jump straight into typing SQL. They apply a structured five-step framework:

1. Clarify the Data Grain: Ask what represents one row in every referenced table. Is orders one row per order, or one row per line item? Identifying cardinality upfront prevents accidental fan-out errors.
2. Isolate Filtering Constraints: Determine whether criteria must filter rows before aggregation (WHERE) or after aggregation (HAVING).
3. Plan the Join Path: Prefer explicit INNER JOIN or LEFT JOIN syntax. Avoid legacy comma-separated joins (FROM tableA, tableB) which obscure Cartesian products.
4. Decompose Complex Calculations into Modular CTEs: Build sub-metrics in readable CTE blocks. This makes debugging effortless and proves code maintainability to interviewers.
5. Verify Edge Cases: Check for null values, division-by-zero risks (NULLIF), tie-breaking criteria in ranking functions, and duplicate keys.

Remember that SQL engines execute clauses in a specific order:

FROM & JOINs  -->  WHERE  -->  GROUP BY  -->  HAVING  -->  SELECT  -->  DISTINCT  -->  WINDOW FUNCTIONS  -->  ORDER BY  -->  LIMIT

Understanding this execution order explains why column aliases defined in SELECT cannot be referenced in WHERE, and why window functions require a CTE or subquery to filter by rank. For an in-depth breakdown, read our guide to SQL Order of Execution.

Master SQL with Live Interactive Verification

Reading queries is only the first step. Building technical confidence requires typing code, handling database syntax errors, and inspecting query plans against real data.

On Topfolio, you can test every query pattern from this guide in our interactive browser sandbox. Practice multi-table joins, CTE aggregations, and window functions with instant automated feedback. All learning modules are 100% free to access, with an optional ₹99 verified certificate available to validate your skills to prospective employers.