Instagram Creator Deals Signed in Q3 2024

Easy
278.

Instagram Creator Deals Signed in Q3 2024

EasyDatesBasics

MySQL

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

ColumnTypeValues
deal_idint
creator_handlevarchar
platformenuminstagram, youtube, moj, x
feeint
signed_ondate

Primary key: deal_id.

One row per signed deal. fee is the agreed amount in rupees.

Example

The rows must come back in the order shown.

InfluencerDeal

deal_idcreator_handleplatformfeesigned_on
1@chaiwithmeerainstagram450002024-07-01
2@techbyrohanyoutube1200002024-08-12
3@fitwithkavyainstagram600002024-09-30
4@desistyleaishainstagram450002024-08-05
5@foodiekabirinstagram800002024-10-01
6@budgetbynehainstagram300002024-06-30

Output

deal_idcreator_handlefee
3@fitwithkavya60000
1@chaiwithmeera45000
4@desistyleaisha45000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
deal_idint
creator_handlevarchar
platformenum(instagram, youtube, moj, x)
feeint
signed_ondate

Primary key: deal_id.

One row per signed deal. fee is the agreed amount in rupees.

Examples

Example 1

InfluencerDeal

deal_idcreator_handleplatformfeesigned_on
1@chaiwithmeerainstagram450002024-07-01
2@techbyrohanyoutube1200002024-08-12
3@fitwithkavyainstagram600002024-09-30
4@desistyleaishainstagram450002024-08-05
5@foodiekabirinstagram800002024-10-01
6@budgetbynehainstagram300002024-06-30

Output

deal_idcreator_handlefee
3@fitwithkavya60000
1@chaiwithmeera45000
4@desistyleaisha45000

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_id

Another 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_id

Another 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 →