SQL Skills Quiz: Test Your Knowledge! Quiz

1. Calculating Click-Through Rate (CTR)

   Given a table of app events with columns event_type, event_date, and app_id, how would you calculate the click-through rate (CTR) for 2022, ensuring you avoid integer division and round the result to two decimal places?

   1. SELECT ROUND(100.0 * SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) / SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END), 2) FROM events WHERE YEAR(event_date) = 2022;
   2. SELECT ROUND(100 * COUNT(clicks) / COUNT(impressions), 2) FROM events WHERE EXTRACT(YEAR FROM event_date) = 2022;
   3. SELECT (SUM(clicks) / SUM(impressions)) * 100 FROM events WHERE event_date LIKE '2022%';
   4. SELECT ROUND(SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) / SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) * 100, 0) FROM events WHERE event_date BETWEEN '2022-01-01' AND '2022-12-31';
   5. SELECT 100.0 * SUM(clicks) / SUM(impressions) AS ctr FROM events WHERE YEAR(event_date) = 2022;

2. Using the CASE Statement in Aggregations

   Which of the following SQL snippets correctly uses the CASE statement to count the number of 'download' events in a table named events?

   1. SELECT SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) FROM events;
   2. SELECT COUNT(CASE WHEN event_type = 'download' THEN 1 END) FROM events;
   3. SELECT SUM(IF event_type = 'download' THEN 1 ELSE 0) FROM events;
   4. SELECT COUNT(*) WHERE event_type = 'download' FROM events;
   5. SELECT SUM(CASE event_type = 'download' THEN 1 ELSE 0 END) events FROM;

3. Preventing Integer Division

   What technique can be used in SQL to ensure the result of a division operation is not affected by integer division?

   1. Multiply one operand by 1.0 or use a decimal such as 100.0 in calculations
   2. Use COUNT() instead of SUM()
   3. Avoid using GROUP BY in queries
   4. Use the DISTINCT keyword on all columns
   5. Add ORDER BY after the division

4. Optimizing Aggregation Queries

   When calculating multiple aggregates like clicks and impressions from the same table, what is a common, more optimized approach?

   1. Use a single query with SUM and CASE statements for conditional aggregation
   2. Create two separate tables and join them after aggregation
   3. Group by every column individually
   4. Perform sub-queries for each aggregate and UNION the results
   5. Write a separate SELECT statement for each calculation

5. Rounding SQL Results

   Which SQL function can be used to round the values of calculated fields to a specified number of decimal places?

   1. ROUND()
   2. TRUNCATE()
   3. FLOOR()
   4. SUM()
   5. ABS()

6. Selecting Data for a Specific Year

   Which condition would you use in a WHERE clause to only include records from the year 2022 for a column named event_date?

   1. WHERE YEAR(event_date) = 2022
   2. WHERE event_date = 2022
   3. WHERE event_date IN 2022
   4. WHERE event_date == '2022'
   5. WHERE event_date CONTAINS '2022'

7. Counting by Event Type

   If you have a table with event_type as either 'click' or 'impression', how would you most efficiently count the number of impressions?

   1. SELECT COUNT(*) FROM events WHERE event_type = 'impression';
   2. SELECT COUNT(impression) FROM events;
   3. SELECT impression FROM events GROUP BY event_type;
   4. SELECT SUM(impression) FROM events;
   5. SELECT COUNT(*) WHERE event_type = 'impression' FROM events;

8. Handling Division by Zero

   In calculating a click-through rate (CTR), what should you add to your query to avoid division by zero when the number of impressions might be zero?

   1. Add a WHERE clause to exclude rows where impressions are zero or use NULLIF or CASE to handle zero denominators
   2. Use LIMIT 0 in your query
   3. Use DISTINCT to remove zeros
   4. CAST clicks as FLOAT
   5. GROUP BY on the impression column

9. Calculating Percentages in SQL

   Given columns clicks and impressions, which expression correctly calculates the percentage (as a float) of clicks out of impressions?

   1. 100.0 * clicks / impressions
   2. clicks / impressions * 100%
   3. impressions / clicks * 100.0
   4. SUM(clicks) - impressions
   5. clicks % impressions

10. Identifying Optimized SQL Solutions

    Given two approaches for calculation: (a) joining two derived tables after separate aggregations, or (b) using SUM with CASE for conditional aggregation in a single query, which is more optimized?

    1. Using SUM with CASE for conditional aggregation in a single query
    2. Always joining two derived tables is more optimized
    3. Using multiple subqueries ensures better optimization
    4. Breaking up logic into as many subqueries as possible improves speed
    5. Using ORDER BY before WHERE optimizes the query