Instagram Creator Deals Signed in Q3 2024 — SQL Problem
The influencer team signs paid deals with creators across platforms.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #278
Problem statement
The influencer team signs paid deals with creators across platforms. For the quarterly review, the brand head wants the Instagram deals signed in the third quarter of 2024 (1 July to 30 September, both included).
Return deal_id, creator_handle and fee for every deal with platform = 'instagram' signed in that quarter, ordered by fee descending, then deal_id ascending.
Tables
Table: InfluencerDeal
| Column | Type |
|---|---|
| deal_id | int |
| creator_handle | varchar |
| platform | enum(instagram, youtube, moj, x) |
| fee | int |
| signed_on | date |
Primary key: deal_id.
One row per signed deal. fee is the agreed amount in rupees.
Examples
Example 1
InfluencerDeal
| deal_id | creator_handle | platform | fee | signed_on |
|---|---|---|---|---|
| 1 | @chaiwithmeera | 45000 | 2024-07-01 | |
| 2 | @techbyrohan | youtube | 120000 | 2024-08-12 |
| 3 | @fitwithkavya | 60000 | 2024-09-30 | |
| 4 | @desistyleaisha | 45000 | 2024-08-05 | |
| 5 | @foodiekabir | 80000 | 2024-10-01 | |
| 6 | @budgetbyneha | 30000 | 2024-06-30 |
Output
| deal_id | creator_handle | fee |
|---|---|---|
| 3 | @fitwithkavya | 60000 |
| 1 | @chaiwithmeera | 45000 |
| 4 | @desistyleaisha | 45000 |
How to solve Instagram Creator Deals Signed in Q3 2024
Two filters: the platform, and the signing date inside the quarter. BETWEEN '2024-07-01' AND '2024-09-30' includes both ends, which is what the statement asks for, and because the column is a date there is no time-of-day to fall past the upper bound. The half-open range >= '2024-07-01' AND < '2024-10-01' is the safer habit, since it stays correct for a datetime column too.
YEAR(signed_on) = 2024 AND QUARTER(signed_on) = 3 reads closest to the question, but wrapping the column in functions stops an index on signed_on from being used. The year check matters: a Q3 deal from 2023 must not slip in. Equal fees are broken by deal_id, so the order is fixed.
Reference solution (MySQL)
SELECT deal_id, creator_handle, fee
FROM InfluencerDeal
WHERE platform = 'instagram'
AND signed_on BETWEEN '2024-07-01' AND '2024-09-30'
ORDER BY fee DESC, deal_idAnother way
SELECT deal_id, creator_handle, fee FROM InfluencerDeal WHERE platform = 'instagram' AND YEAR(signed_on) = 2024 AND QUARTER(signed_on) = 3 ORDER BY fee DESC, deal_idAnother way
SELECT deal_id, creator_handle, fee FROM InfluencerDeal WHERE platform = 'instagram' AND signed_on >= '2024-07-01' AND signed_on < '2024-10-01' ORDER BY fee DESC, deal_id← Email Campaigns That Drove Two or More Unsubscribes · Paid Media Spend per Channel in March 2024 →