Accounts With Two or More Kids Profiles — SQL Problem
The family plan's marketing targets households with several children. A profile flagged is_kids = 1 is a kids profile.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #476
Problem statement
The family plan's marketing targets households with several children. A profile flagged is_kids = 1 is a kids profile.
Return every account with at least two kids profiles, with the columns account_id, total_profiles (all its profiles, kids or not) and kids_profiles. Return the rows in any order.
Tables
Table: Profile
| Column | Type |
|---|---|
| profile_id | int |
| account_id | int |
| profile_name | varchar |
| is_kids | bool |
Primary key: profile_id.
One row per viewer profile; an account holds one to five.
Examples
Example 1
Profile
| profile_id | account_id | profile_name | is_kids |
|---|---|---|---|
| 1 | 501 | Priya | 0 |
| 2 | 501 | Chintu | 1 |
| 3 | 501 | Golu | 1 |
| 4 | 502 | Vikram | 0 |
| 5 | 502 | Kids | 1 |
| 6 | 503 | Ira | 1 |
| 7 | 503 | Zoya | 1 |
Output
| account_id | total_profiles | kids_profiles |
|---|---|---|
| 501 | 3 | 2 |
| 503 | 2 | 2 |
How to solve Accounts With Two or More Kids Profiles
The answer has one row per account, so it is a GROUP BY account_id. The catch is that the row needs two different counts: every profile, and only the kids ones. Filtering with WHERE is_kids = 1 would make the first count wrong, because the adult profiles would be gone before grouping.
Conditional aggregation solves it: COUNT(*) counts every profile of the group, and SUM(CASE WHEN is_kids = 1 THEN 1 ELSE 0 END) adds one only for kids profiles. Because the flag is already 0/1, SUM(is_kids) gives the same number. The condition on that count goes in HAVING, which runs after grouping, not in WHERE.
An alternative counts kids profiles in a derived table with WHERE + HAVING and joins it back to count all profiles — two passes instead of one. The single grouped pass is linear in the number of profiles.
Reference solution (MySQL)
SELECT account_id, COUNT(*) AS total_profiles,
SUM(CASE WHEN is_kids = 1 THEN 1 ELSE 0 END) AS kids_profiles
FROM Profile
GROUP BY account_id
HAVING SUM(CASE WHEN is_kids = 1 THEN 1 ELSE 0 END) >= 2Another way
SELECT account_id, COUNT(*) AS total_profiles, SUM(is_kids) AS kids_profiles FROM Profile GROUP BY account_id HAVING SUM(is_kids) > 1Another way
SELECT p.account_id, COUNT(*) AS total_profiles, k.kids AS kids_profiles
FROM Profile p JOIN (SELECT account_id, COUNT(*) AS kids FROM Profile WHERE is_kids = 1 GROUP BY account_id HAVING COUNT(*) >= 2) k ON k.account_id = p.account_id
GROUP BY p.account_id, k.kids