Interview Query

Ad Engagement Analysis for Qualified Users

Start Timer

0:00:00

Upvote
1
Downvote
Save question
Mark as completed
View comments (1)
Next question

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

Loading comments