Join impressions and messages to find percentile rank

Question

Write a query joining impressions and clicks to produce the combined percentile rank.

Codemia · Accepted Answer

In this problem, we are tasked with joining two datasets: `impressions` and `clicks`. Each dataset contains information about user interactions with ads. The `impressions` table includes fields such as `ad_id`, `user_id`, and `impression_time`, while the `clicks` table includes similar fields but indicates which impressions resulted in clicks. The goal is to compute the combined percentile rank of the clicks based on the impressions data. This means we need to determine the relative standing of each ad based on the number of clicks it received compared to the total impressions.

To accomplish this, we can break down our query into several steps:
1. **Join the Tables**: Use a LEFT JOIN to combine `impressions` and `clicks` on `ad_id` and `user_id`, ensuring we still include impressions with no clicks.
2. **Aggregate Clicks**: Use a GROUP BY clause to count the number of clicks for each ad.
3. **Calculate Percentile Rank**: Use a window function to compute the percentile rank of the number of clicks across all ads.
4. **COALESCE Handling**: Ensure to handle NULL values gracefully using COALESCE whenever necessary to avoid skewing the percentile calculation.

Here is the SQL query that implements the aforementioned steps:
```sql
WITH ad_clicks AS (
    SELECT 
        i.ad_id, 
        COUNT(c.user_id) AS click_count
    FROM 
        impressions i
    LEFT JOIN 
        clicks c ON i.ad_id = c.ad_id AND i.user_id = c.user_id
    GROUP BY 
        i.ad_id
),
ranked_ads AS (
    SELECT 
        ad_id, 
        click_count, 
        PERCENT_RANK() OVER (ORDER BY click_count DESC) AS percentile_rank
    FROM 
        ad_clicks
)
SELECT * FROM ranked_ads;
```

To optimize this query for large datasets, consider the following strategies:
1. **Indexing**: Create indexes on `ad_id` and `user_id` in both `impressions` and `clicks` tables to speed up the JOIN operation.
2. **Partitioning**: If the dataset is extremely large, partition the tables by date or another logical segment to reduce the amount of data processed in each query.
3. **Materialized Views**: For frequently queried data, consider using materialized views to precompute and store the results of the joins and aggregations, reducing compute time during query execution.

When dealing with this query, we should be aware of several edge cases:
1. **NULL Values**: If there are NULLs in the `user_id` or `ad_id`, they should be handled to avoid incorrect joins. Using COALESCE can help mitigate this.
2. **Duplicates**: If the same user clicks on the same ad multiple times, we need to ensure that our aggregation correctly counts unique clicks or total clicks as required.
3. **Empty Tables**: If either `impressions` or `clicks` is empty, the result set should still gracefully return an empty result rather than erroring out.

Join impressions and messages to find percentile rank

Adobe

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Problem Understanding

Approach

Submit Your Answer

Adobe Data Scientist Interview Guide

Related Questions

Optimize a slow query on products

Calculate conversion rate per user from orders

Window function: rank over sessions

Optimize a slow query on orders

Write a query to find time to first action from sessions