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 |
Recommended questions for you
Personalized based on your user activity, skill level, and preferences.
.
.
.
.
.
.
.
.
.
Comments