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
| Column | Type |
|---|---|
| account_id | int |
| business_name | varchar |
| monthly_limit | int |
Primary key: account_id.
One row per ad account; monthly_limit is in rupees.
Table: AdSpend
| Column | Type |
|---|---|
| spend_id | int |
| account_id | int |
| spend_date | date |
| amount | int |
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_id | business_name | monthly_limit |
|---|---|---|
| 1 | Chai Point Express | 50000 |
| 2 | Rangoli Sarees | 30000 |
| 3 | FitBharat Studio | 40000 |
| 4 | Kochi Spice Traders | 20000 |
AdSpend
| spend_id | account_id | spend_date | amount |
|---|---|---|---|
| 1 | 1 | 2025-09-03 | 30000 |
| 2 | 1 | 2025-09-20 | 25000 |
| 3 | 2 | 2025-09-10 | 30000 |
| 4 | 2 | 2025-08-31 | 9000 |
| 5 | 3 | 2025-09-30 | 41000 |
| 6 | 4 | 2025-10-01 | 25000 |
| 7 | 3 | 2025-09-01 | 500 |
Output
| account_id | business_name | september_spend |
|---|---|---|
| 1 | Chai Point Express | 55000 |
| 3 | FitBharat Studio | 41500 |
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_limitAnother 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 →