SQL Essentials: Ratios, Categories, and Cumulative Sums (Easy) Quiz

1. Computing a simple ratio safely

   Which SQL expression correctly computes the ratio of sales to visits as a decimal while safely handling zero visits when the values are stored in columns sales and visits?

   1. 1.0 * sales / NULLIF(visits, 0)
   2. sales / visits
   3. CAST(sales / visits AS DECIMAL)
   4. sales / COALESCE(visits, 0)
   5. sales DIV visits

2. Share of total with window functions

   In a table clicks(user_id) where each row represents one click, which query returns each user's share of total clicks as a percentage?

   1. SELECT user_id, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct FROM clicks GROUP BY user_id
   2. SELECT user_id, 100 * COUNT(*) / COUNT(*) OVER (PARTITION BY user_id) AS pct FROM clicks
   3. SELECT user_id, 100.0 * COUNT(*) / SUM(*) OVER () AS pct FROM clicks GROUP BY user_id
   4. SELECT user_id, 100.0 * AVG(*) OVER () AS pct FROM clicks
   5. SELECT 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct FROM clicks

3. Categorizing prices with CASE

   Which CASE expression categorizes a product with price 9.99 as 'Low', price from 10.00 up to 19.99 as 'Mid', and price 20.00 or more as 'High'?

   1. CASE WHEN price u003C 10 THEN 'Low' WHEN price u003C 20 THEN 'Mid' ELSE 'High' END AS price_band
   2. CASE WHEN price u003C= 10 THEN 'Low' WHEN price u003C 20 THEN 'Mid' ELSE 'High' END AS price_band
   3. CASE WHEN price u003C 10 THEN 'Low' WHEN price BETWEEN 10 AND 19 THEN 'Mid' ELSE 'High' END AS price_band
   4. CASE price u003C 10 THEN 'Low' ELSEIF price u003C 20 THEN 'Mid' ELSE 'High' END AS price_band
   5. CASE WHEN price u003C= 9 THEN 'Low' WHEN price u003C 19 THEN 'Mid' ELSE 'High' END AS price_band

4. Inclusive range for a grade

   You need to label exam scores as 'C' only when the score is between 70 and 79 inclusive; which condition should you use in a CASE expression?

   1. score BETWEEN 70 AND 79
   2. score u003E= 70 AND score u003C 79
   3. score u003E 70 AND score u003C 79
   4. score IN (70, 79)
   5. score BETWEN 70 AND 79

5. Running total per user

   Given a table payments(user_id, dt, amount), which query computes each user's running total of amount ordered by dt?

   1. SELECT user_id, dt, SUM(amount) OVER (PARTITION BY user_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_amount FROM payments
   2. SELECT user_id, dt, SUM(amount) AS running_amount FROM payments GROUP BY user_id, dt
   3. SELECT user_id, dt, LAG(amount) OVER (PARTITION BY user_id ORDER BY dt) AS running_amount FROM payments
   4. SELECT user_id, dt, SUM(amount) OVER (PARTITION BY user_id) AS running_amount FROM payments
   5. SELECT user_id, dt, SUM(amount) OVER ORDER BY dt PARTITION BY user_id AS running_amount FROM payments

6. Previous row value for day-over-day

   For a table steps(d, steps) with one row per day, which window function returns the previous day's steps so you can compute a day-over-day change?

   1. LAG(steps) OVER (ORDER BY d)
   2. LEAD(steps) OVER (ORDER BY d DESC)
   3. FIRST_VALUE(steps) OVER (ORDER BY d)
   4. ROW_NUMBER() OVER (ORDER BY d) - 1
   5. LAGR(steps) OVER (ORDER BY d)

7. Avoiding double counting when joining

   To compute a conversion rate per campaign as purchases divided by views, which approach avoids double counting by aggregating each table before joining?

   1. SELECT v.campaign_id, 1.0 * p.purchases / NULLIF(v.views, 0) AS conv_rate FROM (SELECT campaign_id, COUNT(*) AS views FROM views GROUP BY campaign_id) v LEFT JOIN (SELECT campaign_id, COUNT(*) AS purchases FROM purchases GROUP BY campaign_id) p ON v.campaign_id = p.campaign_id
   2. SELECT campaign_id, 1.0 * COUNT(purchases.*) / NULLIF(COUNT(views.*), 0) AS conv_rate FROM views JOIN purchases ON views.campaign_id = purchases.campaign_id GROUP BY campaign_id
   3. SELECT campaign_id, COUNT(*) / COUNT(*) AS conv_rate FROM views CROSS JOIN purchases GROUP BY campaign_id
   4. SELECT campaign_id, 1.0 * SUM(purchases) / SUM(views) AS conv_rate FROM metrics GROUP BY campaign_id
   5. SELECT DISTINCT campaign_id, 1.0 * COUNT(*) / NULLIF(COUNT(*), 0) AS conv_rate FROM views GROUP BY campaign_id

8. Categorizing with NULL handling

   Which CASE expression produces 'Unknown' for NULL length, 'Small' for 0–9, 'Medium' for 10–19, and 'Large' for 20 or more when classifying a column named length?

   1. CASE WHEN length IS NULL THEN 'Unknown' WHEN length u003C 10 THEN 'Small' WHEN length u003C 20 THEN 'Medium' ELSE 'Large' END AS size_label
   2. CASE WHEN length = NULL THEN 'Unknown' WHEN length u003C= 10 THEN 'Small' WHEN length u003C 20 THEN 'Medium' ELSE 'Large' END AS size_label
   3. CASE WHEN length u003C 10 THEN 'Small' WHEN length u003C 20 THEN 'Medium' WHEN length IS NULL THEN 'Unknown' ELSE 'Large' END AS size_label
   4. CASE WHEN length u003C 10 THEN 'Small' WHEN length u003C 20 THEN 'Medium' END AS size_label
   5. CASE WHEN length u003C= 9 THEN 'Small' WHEN length BETWEEN 10 AND 20 THEN 'Medium' ELSE 'Large' END AS size_label

9. Resetting cumulative sums by group

   When computing a year-to-date cumulative sum of revenue per department ordered by date, which clause ensures the running total resets for each department?

   1. PARTITION BY department
   2. GROUP BY date
   3. ORDER BY department
   4. PARTITION ON department
   5. PARTION BY department

10. Overall completion rate

    In a table orders(id, status) where status is 'completed' or 'pending', which expression returns the overall completion rate as a decimal between 0 and 1?

    1. SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)
    2. COUNT(status = 'completed') / COUNT(*)
    3. AVG(status = 'completed')
    4. SUM(status) / COUNT(*)
    5. SUM(CASE WHEN status = 'completed' THEN 1 END) / COUNT(*)