Cheapest channel per campaign
mediumCheapest channel per campaign
Nykaa SQL Interview Question
Nykaa moves budget toward whichever channel buys a campaign's orders most cheaply, and that answer differs by campaign: influencers work for a launch, search works for a repeat purchase. The measure is money spent per order the channel brought in, worked out over the whole run rather than one day.
For each campaign, return the campaign, the channel with the lowest cost per order, and that figure as cost_per_order, rounded to 2 decimal places.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Scientist
nykaa_campaign_spendTable24 rows
| Column Name | Type |
|---|---|
| spend_id | BIGINT |
| campaign | VARCHAR |
| channel | VARCHAR |
| spend_on | DATE |
| spend | DOUBLE |
| attributed_orders | BIGINT |
nykaa_campaign_spendExample Input
| spend_id | campaign | channel | spend_on | spend | attributed_orders |
|---|---|---|---|---|---|
| 4109 | Monsoon Care | Search | 2024-05-01 | 38000 | 266 |
| 4110 | Monsoon Care | Social | 2024-05-01 | 41000 | 255 |
| 4111 | Monsoon Care | Influencer | 2024-05-01 | 52000 | 140 |
| 4112 | Monsoon Care | Search | 2024-05-02 | 39500 | 281 |
| 4113 | Monsoon Care | Social | 2024-05-02 | 43000 | 262 |
| 4114 | Monsoon Care | Influencer | 2024-05-02 | 55000 | 158 |
| 4117 | Festive Kajal | Search | 2024-05-01 | 72000 | 540 |
| 4118 | Festive Kajal | Social | 2024-05-01 | 95000 | 612 |
| 4119 | Festive Kajal | Search | 2024-05-02 | 74500 | 558 |
| 4120 | Festive Kajal | Social | 2024-05-02 | 92000 | 601 |
| 4121 | Festive Kajal | Influencer | 2024-05-02 | 120000 | 330 |
Example Output
| campaign | channel | cost_per_order |
|---|---|---|
| Festive Kajal | Search | 133.42 |
| Monsoon Care | Search | 141.68 |
Explanation
Search buys orders most cheaply in both campaigns in the example, at 133.42 and 141.68 for each order. The figure comes from the total spend over the total orders, not from averaging the daily numbers.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Nykaa
Difficulty
medium
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...