Meta: Ad Accounts That Overspent Their September Limit

Easy
751.

Meta: Ad Accounts That Overspent Their September Limit

Every ad account on Meta's ads platform carries a monthly spending limit, and the billing-risk team reviews accounts that went past it.

Return the accounts whose total spend in September 2025 (2025-09-01 to 2025-09-30 inclusive) is strictly greater than their monthly_limit, with the columns account_id, business_name and september_spend. Spend on other dates does not count, and spend exactly equal to the limit is not over it. Return the rows in any order.

A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.

Tables

Table: AdAccount

ColumnType
account_idint
business_namevarchar
monthly_limitint

Primary key: account_id.

One row per ad account; monthly_limit is in rupees.

Table: AdSpend

ColumnType
spend_idint
account_idint
spend_datedate
amountint

Primary key: spend_id.

One row per billed charge; an account can have several on one day. account_id always names an account.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AdAccount

account_idbusiness_namemonthly_limit
1Chai Point Express50000
2Rangoli Sarees30000
3FitBharat Studio40000
4Kochi Spice Traders20000

AdSpend

spend_idaccount_idspend_dateamount
112025-09-0330000
212025-09-2025000
322025-09-1030000
422025-08-319000
532025-09-3041000
642025-10-0125000
732025-09-01500

Output

account_idbusiness_nameseptember_spend
1Chai Point Express55000
3FitBharat Studio41500
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.

Meta: Ad Accounts That Overspent Their September Limit

Every ad account on Meta's ads platform carries a monthly spending limit, and the billing-risk team reviews accounts that went past it.

  • Difficulty: Easy
  • Topics: Aggregation, Joins, Dates
  • Dialect: MySQL
  • Problem: #751

Problem statement

Every ad account on Meta's ads platform carries a monthly spending limit, and the billing-risk team reviews accounts that went past it.

Return the accounts whose total spend in September 2025 (2025-09-01 to 2025-09-30 inclusive) is strictly greater than their monthly_limit, with the columns account_id, business_name and september_spend. Spend on other dates does not count, and spend exactly equal to the limit is not over it. Return the rows in any order.

A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.

Tables

Table: AdAccount

ColumnType
account_idint
business_namevarchar
monthly_limitint

Primary key: account_id.

One row per ad account; monthly_limit is in rupees.

Table: AdSpend

ColumnType
spend_idint
account_idint
spend_datedate
amountint

Primary key: spend_id.

One row per billed charge; an account can have several on one day. account_id always names an account.

Examples

Example 1

AdAccount

account_idbusiness_namemonthly_limit
1Chai Point Express50000
2Rangoli Sarees30000
3FitBharat Studio40000
4Kochi Spice Traders20000

AdSpend

spend_idaccount_idspend_dateamount
112025-09-0330000
212025-09-2025000
322025-09-1030000
422025-08-319000
532025-09-3041000
642025-10-0125000
732025-09-01500

Output

account_idbusiness_nameseptember_spend
1Chai Point Express55000
3FitBharat Studio41500

How to solve Meta: Ad Accounts That Overspent Their September Limit

The rule compares a total with a per-account number, so the plan is: keep only September's charges, add them up per account, and keep the accounts whose total beats the limit. The date filter is a WHERE (it removes rows before grouping); the comparison with the sum is a HAVING (it removes whole groups after).

HAVING SUM(s.amount) > a.monthly_limit needs monthly_limit to be a grouped value, so it is listed in GROUP BY — it is one value per account anyway. The comparison is strict, so an account that spent exactly its limit stays out, and the charges on 31 August and 1 October fall outside BETWEEN '2025-09-01' AND '2025-09-30'. An account with no September charges forms no group and is never reported.

The alternative computes the September totals in a derived table first and compares them in an ordinary WHERE after joining, using YEAR/MONTH for the date test. Both scan the charges once.

Reference solution (MySQL)

SELECT a.account_id, a.business_name, SUM(s.amount) AS september_spend
FROM AdAccount a
JOIN AdSpend s ON s.account_id = a.account_id
WHERE s.spend_date BETWEEN '2025-09-01' AND '2025-09-30'
GROUP BY a.account_id, a.business_name, a.monthly_limit
HAVING SUM(s.amount) > a.monthly_limit

Another way

SELECT a.account_id, a.business_name, t.september_spend
FROM AdAccount a
JOIN (SELECT account_id, SUM(amount) AS september_spend FROM AdSpend
      WHERE YEAR(spend_date) = 2025 AND MONTH(spend_date) = 9 GROUP BY account_id) t ON t.account_id = a.account_id
WHERE t.september_spend > a.monthly_limit

← Meta: Going and Interested Counts per Facebook Event · Meta: Threads Posts Cut Off in the Feed Preview →