Accounts With Two or More Kids Profiles

Easy
476.

Accounts With Two or More Kids Profiles

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

ColumnType
profile_idint
account_idint
profile_namevarchar
is_kidsbool

Primary key: profile_id.

One row per viewer profile; an account holds one to five.

Example

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

Profile

profile_idaccount_idprofile_nameis_kids
1501Priya0
2501Chintu1
3501Golu1
4502Vikram0
5502Kids1
6503Ira1
7503Zoya1

Output

account_idtotal_profileskids_profiles
50132
50322
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.

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

ColumnType
profile_idint
account_idint
profile_namevarchar
is_kidsbool

Primary key: profile_id.

One row per viewer profile; an account holds one to five.

Examples

Example 1

Profile

profile_idaccount_idprofile_nameis_kids
1501Priya0
2501Chintu1
3501Golu1
4502Vikram0
5502Kids1
6503Ira1
7503Zoya1

Output

account_idtotal_profileskids_profiles
50132
50322

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) >= 2

Another way

SELECT account_id, COUNT(*) AS total_profiles, SUM(is_kids) AS kids_profiles FROM Profile GROUP BY account_id HAVING SUM(is_kids) > 1

Another 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

← Watch Hours by Genre · Tracks With a Featured Artist →