How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Molocoads.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Molocoads during technical interviews.

Find top advertisers and cost per KPI | Molocoads SQL Question

Quick Overview

This question evaluates proficiency in data manipulation and analytical SQL/Python skills, including aggregation, ranking with ties, dataset joins at a defined grain, date-based filtering relative to campaign launch dates, and handling division-by-zero scenarios when computing cost metrics.

Find top advertisers and cost per KPI

Company: Molocoads

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

You are given three advertising campaign tables. **Table: `campaign_spend`** | Column | Type | Description | |---|---:|---| | `date` | DATE | Calendar date in UTC. | | `advertiser_id` | BIGINT | Advertiser identifier. | | `campaign_id` | BIGINT | Campaign identifier. | | `creative_group` | VARCHAR | Creative group within a campaign. | | `ad_spend` | DECIMAL | Amount spent on ads for the row. | | `impressions` | BIGINT | Number of ad impressions. | **Table: `campaign_conversion`** | Column | Type | Description | |---|---:|---| | `date` | DATE | Calendar date in UTC. | | `advertiser_id` | BIGINT | Advertiser identifier. | | `campaign_id` | BIGINT | Campaign identifier. | | `creative_group` | VARCHAR | Creative group within a campaign. | | `installs` | BIGINT | Number of installs attributed to the campaign. | | `kpi_cnt` | BIGINT | Final KPI conversion count. Different campaigns may optimize for different KPIs, but this column stores the final conversion count for that campaign's KPI. | **Table: `campaign_goal`** | Column | Type | Description | |---|---:|---| | `campaign_id` | BIGINT | Campaign identifier. | | `campaign_goal` | VARCHAR | Campaign optimization goal, such as install, purchase, signup, or other KPI type. | | `campaign_launch_date` | DATE | Campaign launch date in UTC. | Assume `campaign_id` maps to exactly one `campaign_goal`. Spend and conversion rows can be joined at the grain of `date`, `advertiser_id`, `campaign_id`, and `creative_group` when both spend and conversion information are needed. Answer the following SQL questions: 1. **Find the top 3 spending advertisers for each day.** - Aggregate spend at the `date` and `advertiser_id` level. - Rank advertisers by total daily spend in descending order. - Return all advertisers tied within the top 3 ranks if ties occur. - Required output columns: `date`, `advertiser_id`, `daily_ad_spend`, `spend_rank`. 2. **Calculate cost per KPI for each campaign goal.** - For each `campaign_goal`, aggregate total ad spend and total KPI conversions across all campaigns with that goal. - Use only spend and conversions on or after each campaign's `campaign_launch_date`. - Define `cost_per_kpi = total_ad_spend / total_kpi_cnt`. - If `total_kpi_cnt` is zero, return `NULL` for `cost_per_kpi` instead of dividing by zero. - Required output columns: `campaign_goal`, `total_ad_spend`, `total_kpi_cnt`, `cost_per_kpi`.

Quick Answer: This question evaluates proficiency in data manipulation and analytical SQL/Python skills, including aggregation, ranking with ties, dataset joins at a defined grain, date-based filtering relative to campaign launch dates, and handling division-by-zero scenarios when computing cost metrics.

Quick Overview