Ad Engagement Analysis for Qualified Users
Start Timer
0:00:00
Let’s say we have tables ad_interactions and ad_types. The ad_interactions table contains millions of rows tracking how users interact with different advertisements on the platform with the interaction_type column consisting of two values: click and impression.
Write a query to find the engagement rate for each ad type. Only include interactions from users who, at any point in the last 48 hours, have seen at least three different ad types. Sort by highest engagement rate
Note: Engagement rate is calculated as (number of clicks / number of impressions) * 100
Tables
ad_interactions table
| Column | Type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| ad_type_id | INTEGER |
| interaction_type | VARCHAR |
| created_at | TIMESTAMP |
ad_types table
| Column | Type |
|---|---|
| id | INTEGER |
| name | VARCHAR |
| category | VARCHAR |
Expected Output
| Column | Type |
|---|---|
| ad_name | VARCHAR |
| engagement_rate | FLOAT |
| total_qualified_impressions | INTEGER |
.
.
.
.
.
.
.
.
.
Comments