In this problem, you’ll compute the Click-Through Rate (CTR) for different apps based on their events recorded in 2022. The CTR is a measure of how often users click on an ad or a link after seeing it, expressed as a percentage. This tutorial will guide you through writing an SQL query to calculate the CTR and format the results to two decimal places.

Problem Overview

You have an events table with the following columns:

• app_id: Identifier for the app.
• event_type: Type of event (either ‘click’ or ‘impression’).
• timestamp: Date and time when the event occurred.

Your goal is to calculate the CTR for each app in 2022, which is defined as:

[ \text{CTR} = \frac{\text{Number of clicks}}{\text{Number of impressions}} \times 100.0 ]

The result should be rounded to two decimal places.

Table: events

Example Data:

Desired Output:

Explanation:

1. Calculate CTR for App 123:

• Impressions: 2
• Clicks: 1
• CTR: (\frac{1}{2} \times 100.0 = 50.00\%)

1. Calculate CTR for App 234:

• Impressions: 1
• Clicks: 1
• CTR: (\frac{1}{1} \times 100.0 = 100.00\%)

SQL Query Breakdown

To calculate the CTR, follow these steps:

1. Count the number of impressions and clicks for each app.
2. Calculate the CTR as a percentage.
3. Round the CTR to two decimal places.

Here’s the SQL query to achieve this:

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 
    YEAR(timestamp) = 2022
GROUP BY 
    app_id;

Query Explanation:

1. Filtering Data for 2022:

• WHERE YEAR(timestamp) = 2022: Ensures we are only considering events from the year 2022.

1. Counting Clicks and Impressions:

• SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END): Counts the number of ‘click’ events.
• SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END): Counts the number of ‘impression’ events.

1. Calculating and Rounding CTR:

• 100.0 * ...: Multiplies the CTR by 100.0 to get the percentage value.
• ROUND(..., 2): Rounds the result to two decimal places.

1. Grouping by App ID:

• GROUP BY app_id: Groups the results by each app to calculate CTR for each app separately.

Conclusion

This SQL query provides a precise calculation of the Click-Through Rate (CTR) for each app, reflecting how effectively each app’s ads or links are engaging users in 2022. By rounding the CTR to two decimal places, the results are presented in a clear and professional format. Feel free to test this query with your dataset to verify the results!