Objective

Write a SQL query to calculate the click-through rate (CTR) for each app based on 2022 events data.

Table Schema:

• events

Task Requirements:

• Filter only events from the year 2022
• Calculate CTR as (clicks / impressions) * 100 for each app
• Round the CTR to 2 decimal places
• Sort results by app ID in ascending order
  
  

Output columns: app_id, ctr

SELECT 
    app_id,
    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
    ) AS ctr
FROM events
WHERE EXTRACT(YEAR FROM event_date) = 2022
GROUP BY app_id
ORDER BY app_id ASC;

Explanation

Step 1: Understanding Click-Through Rate (CTR)

CTR measures the percentage of impressions that resulted in clicks. Formula: CTR = (Total Clicks / Total Impressions) × 100.

Step 2: Filtering Events from 2022

WHERE EXTRACT(YEAR FROM event_date) = 2022

Filters to only include records from the year 2022.

Step 3: Counting Clicks and Impressions

SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END)
SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END)

CASE statements conditionally count only click events and impression events respectively.

Step 4: Calculating and Rounding CTR

ROUND(100.0 * clicks / impressions, 2)

Multiplies by 100 to convert to percentage; 100.0 ensures floating-point division; ROUND(..., 2) gives 2 decimal places.

Step 5: Grouping and Ordering

GROUP BY app_id
ORDER BY app_id ASC

Groups by app_id to calculate CTR per app, sorted by app ID for organized output.