Aggregation Problems

Problem 1 — First Login Analysis

Problem Statement

In any gaming platform, it is important to know when each player first started using the platform. This helps in:

• user lifecycle tracking
• cohort analysis
• retention analysis
• onboarding analytics

You are given a table named Activity.

Table Structure

Important Notes

• (player_id, event_date) is the primary key.
• A player can appear only once per day.
• A player may use different devices.
• We need to find the first login date for every player.

Example Input

Expected Output

CREATE TABLE Activity (player_id INTEGER, device_id INTEGER, event_date TEXT, games_played INTEGER);
INSERT INTO Activity VALUES
  (1, 2, '2016-03-01', 5),
  (1, 2, '2016-05-02', 6),
  (2, 3, '2017-06-25', 1),
  (3, 1, '2016-03-02', 0),
  (3, 4, '2018-07-03', 5);

Intuition

Whenever you see words like:

• first
• earliest
• minimum date
• oldest record

…your brain should immediately think:

MIN()

because:

• earlier dates are smaller dates
• MIN(date_column) gives the earliest date

Mental Model

Think of SQL creating buckets for each player.

Example:

Player 1
 ├── 2016-03-01
 └── 2016-05-02

Player 2
 └── 2017-06-25

Player 3
 ├── 2016-03-02
 └── 2018-07-03

Now SQL picks:

MIN(date)

from every bucket.

Approach

Step 1 — Group All Rows by player_id

We want one result per player.

So we use:

GROUP BY player_id

This creates separate groups for every player.

Step 2 — Find the Earliest Date

Inside each player group:

MIN(event_date)

returns the first login date.

Step 3 — Rename the Output Column

We use:

AS first_login

to make the result more readable.

Step 4 — Sort Final Output

The question asks to return results ordered by player_id.

So we use:

ORDER BY player_id

SQL Query

SELECT
    player_id,

    -- Find the earliest login date for each player
    MIN(event_date) AS first_login

FROM Activity

-- Create one group per player
GROUP BY player_id

-- Sort final output by player_id
ORDER BY player_id;

Internal Execution Flow

Original Data

Player 1 → 2016-03-01, 2016-05-02
Player 2 → 2017-06-25
Player 3 → 2016-03-02, 2018-07-03

After GROUP BY

Group 1 → Player 1
Group 2 → Player 2
Group 3 → Player 3

After MIN()

Player 1 → 2016-03-01
Player 2 → 2017-06-25
Player 3 → 2016-03-02

Why MIN() Works on Dates

Dates are internally comparable.

Example:

2016-03-01 < 2016-05-02

So:

MIN(event_date)

naturally gives the earliest date.

Time Complexity

Complexity

Time Complexity: O(N)

N = total rows in Activity table

SQL scans all rows once and groups them.

Common Mistakes to Avoid

Mistake 1 — Forgetting GROUP BY

Wrong:

SELECT MIN(event_date)
FROM Activity;

This returns:

only ONE global earliest date

instead of one per player.

Mistake 2 — Using ORDER BY Instead of MIN()

Wrong approach:

ORDER BY event_date

Sorting does NOT solve aggregation.

We need one row per player, not sorted rows.

Mistake 3 — Selecting Non-Grouped Columns

Wrong:

SELECT player_id, device_id, MIN(event_date)
FROM Activity
GROUP BY player_id;

Why wrong?

Because:

Which device_id should SQL show?

A player may use multiple devices.

Key SQL Concepts Learned

Final Mental Shortcut

Whenever the question says:

• first login
• first order
• first transaction
• earliest date

Think:

MIN(date_column)
GROUP BY entity

Problem 2 — Employee Work Time Summary

Problem Statement

In a workplace with flexible working hours, employees may enter and leave the office multiple times during the same day.

As an HR analyst, your task is to calculate:

Total time spent in the office

for every employee on each day.

This helps with:

• attendance tracking
• payroll calculations
• overtime analysis
• productivity monitoring

Table Structure

Important Notes

• (emp_id, event_day, in_time) is the primary key.
• One employee can have multiple entries in a single day.
• in_time < out_time
• Time is stored in minutes from 00:00.

Example Input

Expected Output

CREATE TABLE Employees (emp_id INTEGER, event_day TEXT, in_time INTEGER, out_time INTEGER);
INSERT INTO Employees VALUES
  (1, '2020-11-28', 4, 32),
  (1, '2020-11-28', 55, 200),
  (1, '2020-12-03', 1, 42),
  (2, '2020-11-28', 3, 33),
  (2, '2020-12-09', 47, 74);

Explanation

Employee 1 on 2020-11-28

(32 - 4) + (200 - 55)
= 28 + 145
= 173

Employee 2 on 2020-11-28

33 - 3 = 30

Employee 1 on 2020-12-03

42 - 1 = 41

Employee 2 on 2020-12-09

74 - 47 = 27

Intuition

This problem is about:

calculating durations
+
adding them together

Whenever you see:

• total working time
• total duration
• total minutes
• total hours
• total spent

…your brain should immediately think:

SUM()

because we need to:

add multiple values together

Mental Model

Think of every office visit as:

out_time - in_time

Example:

Employee 1

Visit 1:
32 - 4 = 28 mins

Visit 2:
200 - 55 = 145 mins

Now SQL simply adds them:

28 + 145 = 173

Approach

Step 1 — Calculate Time Spent Per Entry

For every row:

out_time - in_time

gives:

time spent during that visit

Step 2 — Group by Employee and Day

We need:

one result per employee per day

So we group using:

GROUP BY event_day, emp_id

This creates separate buckets like:

(2020-11-28, Employee 1)
(2020-11-28, Employee 2)
(2020-12-03, Employee 1)

Step 3 — Add All Visit Durations

Now SQL runs:

SUM(out_time - in_time)

inside every bucket.

This gives total office time for that employee on that day.

Step 4 — Rename Columns

We use aliases for cleaner output:

event_day AS day

and

SUM(...) AS total_time

Step 5 — Sort Final Output

The question asks:

order by day and employee

So we use:

ORDER BY event_day, emp_id

SQL Query

SELECT
    -- Rename event_day column as day
    event_day AS day,

    -- Employee ID
    emp_id,

    -- Total office time for that employee on that day
    SUM(out_time - in_time) AS total_time

FROM Employees

-- Create one group per employee per day
GROUP BY event_day, emp_id

-- Sort final result
ORDER BY event_day, emp_id;

Internal Execution Flow

Original Data

Emp 1 → 2020-11-28 → 4 → 32
Emp 1 → 2020-11-28 → 55 → 200
Emp 1 → 2020-12-03 → 1 → 42
Emp 2 → 2020-11-28 → 3 → 33
Emp 2 → 2020-12-09 → 47 → 74

Step 1 — Calculate Duration Per Row

32 - 4 = 28
200 - 55 = 145
42 - 1 = 41
33 - 3 = 30
74 - 47 = 27

Step 2 — GROUP BY Creates Buckets

(2020-11-28, Emp 1)
→ 28, 145

(2020-11-28, Emp 2)
→ 30

(2020-12-03, Emp 1)
→ 41

(2020-12-09, Emp 2)
→ 27

Step 3 — SUM Runs Inside Each Bucket

Emp 1 → 173
Emp 2 → 30
Emp 1 → 41
Emp 2 → 27

Why SUM() is Perfect Here

Because:

we need TOTAL time

and totals always mean:

SUM()

Time Complexity

Time Complexity: O(N)

N = total rows in Employees table

SQL scans all rows once.

Common Mistakes to Avoid

Mistake 1 — Forgetting SUM()

Wrong:

SELECT out_time - in_time
FROM Employees;

This only gives:

individual visit durations

NOT total daily office time.

Mistake 2 — Grouping Only by emp_id

Wrong:

GROUP BY emp_id

Why wrong?

Because:

different dates will merge together

We need:

separate totals per day

Mistake 3 — Using MIN() or MAX()

This problem is NOT asking:

• earliest entry
• latest exit

It asks:

total time spent

So we must use:

SUM()

Mistake 4 — Forgetting ORDER BY

Without sorting:

output order becomes unpredictable

Always follow ordering instructions carefully.

Key SQL Concepts Learned

Final Mental Shortcut

Whenever the question says:

• total time
• total hours
• total sales
• total amount
• total duration

Think:

SUM()

And whenever the question says:

per employee
per day
per category
per user

Think:

GROUP BY

Problem 3 — Unique Subjects per Teacher

Problem Statement

At a university, teachers may teach the same subject in multiple departments.

However, for reporting purposes, the university administration wants to know:

How many UNIQUE subjects does each teacher teach?

Even if the same subject is taught in multiple departments, it should only be counted once.

Table Structure

Important Notes

• (teacher_id, subject_id, dept_id) is the primary key.
• One subject can appear in multiple departments.
• We only care about:

unique subjects per teacher

NOT departments.

Example Input

Expected Output

CREATE TABLE Teacher (teacher_id INTEGER, subject_id INTEGER, dept_id INTEGER);
INSERT INTO Teacher VALUES
  (1, 2, 3),
  (1, 2, 4),
  (1, 3, 3),
  (2, 1, 1),
  (2, 2, 1),
  (2, 3, 1),
  (2, 4, 1);

Explanation

Teacher 1

Subject 2 → taught in dept 3 and 4
BUT counted only once

Subject 3 → counted once

Total unique subjects:

2

Teacher 2

Subjects:
1, 2, 3, 4

All are unique.

Total:

4

Intuition

This problem is testing one very important SQL concept:

COUNT(DISTINCT ...)

Whenever you see words like:

• unique
• distinct
• remove duplicates
• count only once

…your brain should immediately think:

COUNT(DISTINCT column)

because normal COUNT() counts duplicates too.

Mental Model

Think of SQL first collecting all subjects for each teacher.

Teacher 1

2
2
3

Now DISTINCT removes duplicates:

2
3

Now COUNT counts remaining values:

2 subjects

Approach

Step 1 — Group Rows by Teacher

We want:

one output row per teacher

So we use:

GROUP BY teacher_id

This creates separate buckets for every teacher.

Step 2 — Remove Duplicate Subjects

A teacher may teach:

same subject
+
different departments

But we only want unique subjects.

So we use:

DISTINCT subject_id

Step 3 — Count Remaining Unique Subjects

Now SQL counts distinct subjects:

COUNT(DISTINCT subject_id)

Step 4 — Rename Output Column

We rename the calculated result as:

cnt

for cleaner output.

SQL Query

SELECT
    -- Teacher ID
    teacher_id,

    -- Count only unique subjects per teacher
    COUNT(DISTINCT subject_id) AS cnt

FROM Teacher

-- Create one group per teacher
GROUP BY teacher_id;

Internal Execution Flow

Original Data

Teacher 1 → 2, 2, 3
Teacher 2 → 1, 2, 3, 4

Step 1 — GROUP BY Creates Buckets

Bucket 1 → Teacher 1
Bucket 2 → Teacher 2

Step 2 — DISTINCT Removes Duplicates

Teacher 1:
2, 2, 3
→ 2, 3

Teacher 2:
1, 2, 3, 4
→ 1, 2, 3, 4

Step 3 — COUNT() Counts Remaining Values

Teacher 1 → 2
Teacher 2 → 4

Why COUNT(DISTINCT) is Perfect Here

Because we need:

unique subject count

NOT:

total rows

Difference Between COUNT() vs COUNT(DISTINCT)

Using Normal COUNT()

COUNT(subject_id)

Teacher 1 would become:

3

because subject 2 appears twice.

That would be WRONG.

Using COUNT(DISTINCT)

COUNT(DISTINCT subject_id)

Duplicates are removed first.

Correct answer:

2

Time Complexity

Time Complexity: O(N)

N = total rows in Teacher table

SQL scans all rows once and groups them.

Common Mistakes to Avoid

Mistake 1 — Using COUNT() Instead of COUNT(DISTINCT)

Wrong:

COUNT(subject_id)

This counts duplicates.

Mistake 2 — Grouping by subject_id

Wrong:

GROUP BY subject_id

We need:

subjects per teacher

So grouping must happen using:

teacher_id

Mistake 3 — Counting Departments

The question is asking:

unique subjects

NOT:

unique departments

Focus carefully on the business requirement.

Mistake 4 — Forgetting DISTINCT

Without DISTINCT:

same subject across multiple departments
gets counted multiple times

Key SQL Concepts Learned

Final Mental Shortcut

Whenever the question says:

• unique
• distinct
• count once
• remove duplicates

Think:

COUNT(DISTINCT column)

And whenever the question says:

per teacher
per user
per category
per project

Think:

GROUP BY

Problem 7 — Actor & Director Frequent Collaborations

Problem Statement

In the film industry, production houses often analyze:

Which actor-director pairs collaborate frequently?

Frequent collaborations may indicate:

• strong creative chemistry
• successful movies
• trusted partnerships
• high audience success rates

You are given an ActorDirector table.

Table Structure

Column Meaning

Goal

Find all:

(actor_id, director_id)

pairs where:

they worked together 3 or more times

Example Input

Expected Output

CREATE TABLE ActorDirector (actor_id INTEGER, director_id INTEGER, timestamp INTEGER);
INSERT INTO ActorDirector VALUES
  (1, 1, 0),
  (1, 1, 1),
  (1, 1, 2),
  (1, 2, 3),
  (1, 2, 4),
  (2, 1, 5),
  (2, 1, 6);

Explanation

Let’s count collaborations:

Only:

(1,1)

has collaborations:

>= 3

So only this pair is returned.

Intuition

This is a classic:

GROUP BY + COUNT + HAVING

problem.

We need to:

1. group all collaborations by actor-director pair
2. count how many times each pair appears
3. keep only pairs with count ≥ 3

Mental Model

Think of SQL creating buckets like:

(1,1) bucket
(1,2) bucket
(2,1) bucket

Now SQL counts:

how many rows exist inside each bucket

Finally:

keep only buckets with 3 or more rows

Approach

Step 1 — Group Collaborations

We group by:

GROUP BY actor_id, director_id

This creates one group for every unique pair.

Step 2 — Count Collaborations

Each row represents:

one collaboration

So:

COUNT(*)

gives total collaborations for that pair.

Step 3 — Filter Frequent Pairs

We only want:

pairs with at least 3 collaborations

So we use:

HAVING COUNT(*) >= 3

Why HAVING Instead of WHERE?

Important concept:

WHERE filters rows BEFORE grouping
HAVING filters groups AFTER grouping

Since:

COUNT(*)

is an aggregated value,

we MUST use:

HAVING

NOT:

WHERE

SQL Query

SELECT
    -- Actor involved in collaboration
    actor_id,

    -- Director involved in collaboration
    director_id

FROM ActorDirector

-- Create one group per actor-director pair
GROUP BY actor_id, director_id

-- Keep only pairs with 3 or more collaborations
HAVING COUNT(*) >= 3;

Internal Execution Flow

Step 1 — GROUP BY Creates Pair Buckets

SQL creates:

(1,1) → [3 rows]
(1,2) → [2 rows]
(2,1) → [2 rows]

Step 2 — COUNT(*) Runs Inside Each Bucket

Step 3 — HAVING Filters Results

Condition:

COUNT(*) >= 3

Remaining result:

Why COUNT(*) Works Perfectly Here

Each row represents:

one actor-director collaboration

So counting rows means:

counting collaborations

Time Complexity

Time Complexity: O(N)

Where:

• N = total rows in ActorDirector table

Why?

• SQL scans rows once
• groups them
• counts inside groups

Common Mistakes to Avoid

Mistake 1 — Using WHERE Instead of HAVING

Wrong:

SELECT actor_id, director_id
FROM ActorDirector
WHERE COUNT(*) >= 3
GROUP BY actor_id, director_id;

This fails because:

WHERE cannot use aggregate functions

Correct:

HAVING COUNT(*) >= 3

Mistake 2 — Forgetting GROUP BY

Wrong:

SELECT actor_id, director_id, COUNT(*)
FROM ActorDirector;

This causes error because:

actor_id and director_id are non-aggregated columns

Mistake 3 — Grouping Only by actor_id

Wrong:

GROUP BY actor_id

This combines:

all directors together

But question asks for:

actor-director PAIRS

So both columns are required.

Mistake 4 — Using COUNT(director_id)

This works here because:

director_id is never NULL

But generally:

COUNT(*)

is cleaner and safer.

Key SQL Concepts Learned

Final Mental Shortcut

Whenever the question says:

• collaborated multiple times
• repeated interactions
• frequent pairs
• users with many actions
• customers with many orders

Think:

GROUP BY
→ COUNT(*)
→ HAVING COUNT(*) >= X

Problem 8 — Popular Classes with At Least 5 Students

Problem Statement

A school administration wants to identify:

popular classes with high student enrollment

This helps in:

• assigning larger classrooms
• allocating teaching assistants
• managing schedules
• planning resources efficiently

You are given a Courses table.

Table Structure

Column Meaning

Important Note

Each:

(student, class)

pair is unique.

This means:

a student cannot appear twice in the same class

Goal

Find all classes where:

number of students >= 5

Example Input

CREATE TABLE Courses (student TEXT, class TEXT);
INSERT INTO Courses VALUES
  ('A', 'Math'),
  ('B', 'English'),
  ('C', 'Math'),
  ('D', 'Biology'),
  ('E', 'Math'),
  ('F', 'Computer'),
  ('G', 'Math'),
  ('H', 'Math'),
  ('I', 'Math');

Expected Output

class

Math

Explanation

Student count per class:

Only:

Math

has:

>= 5 students

So it is returned.

Intuition

This is another classic:

GROUP BY + COUNT + HAVING

problem.

We need to:

1. create one group per class
2. count students inside each class
3. keep only classes with at least 5 students

Mental Model

Think of SQL creating buckets:

Math bucket
English bucket
Biology bucket
Computer bucket

Now SQL counts:

how many students exist inside each bucket

Finally:

keep only buckets with 5 or more students

Approach

Step 1 — Group by Class

We want:

one row per class

So we use:

GROUP BY class

Step 2 — Count Students

Each row represents:

one student enrollment

So:

COUNT(student)

gives total students in that class.

Step 3 — Filter Classes

We only need classes where:

student count >= 5

So we use:

HAVING COUNT(student) >= 5

Why HAVING Instead of WHERE?

Important SQL concept:

WHERE filters rows
HAVING filters groups

Since:

COUNT(student)

is an aggregated value,

we MUST use:

HAVING

NOT:

WHERE

SQL Query

SELECT
    -- Class name
    class

FROM Courses

-- Create one group per class
GROUP BY class

-- Keep only classes with at least 5 students
HAVING COUNT(student) >= 5;

Internal Execution Flow

Step 1 — GROUP BY Creates Class Buckets

SQL internally creates:

Math → [6 rows]
English → [1 row]
Biology → [1 row]
Computer → [1 row]

Step 2 — COUNT(student)

Step 3 — HAVING Filters Results

Condition:

COUNT(student) >= 5

Remaining result:

class

Math

Why COUNT(student) Works Here

Because:

student column is never NULL

So:

COUNT(student)

correctly counts all enrollments.

Alternative Safer Version

In real-world SQL:

COUNT(*)

is often preferred.

Equivalent query:

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5;

Time Complexity

Time Complexity: O(N)

Where:

• N = total rows in Courses table

Why?

• SQL scans rows once
• groups by class
• counts students

Common Mistakes to Avoid

Mistake 1 — Using WHERE with COUNT

Wrong:

SELECT class
FROM Courses
WHERE COUNT(student) >= 5
GROUP BY class;

This fails because:

WHERE cannot use aggregate functions

Correct:

HAVING COUNT(student) >= 5

Mistake 2 — Forgetting GROUP BY

Wrong:

SELECT class, COUNT(student)
FROM Courses;

This causes error because:

class is non-aggregated

Mistake 3 — Grouping by Student Instead of Class

Wrong:

GROUP BY student

This creates:

one group per student

But the question asks:

student count per class

Mistake 4 — Using COUNT(DISTINCT student)

Not needed here because:

(student, class) pairs are already unique

So normal COUNT works perfectly.

Key SQL Concepts Learned

Final Mental Shortcut

Whenever the question says:

• classes with many students
• departments with many employees
• customers with many orders
• categories with many products
• events with many registrations

Think:

GROUP BY
→ COUNT()
→ HAVING COUNT() >= X

Problem 9 — Find Duplicate Email Addresses

Problem Statement

In modern user account systems, duplicate email registrations can create serious problems such as:

• login confusion
• duplicate accounts
• data inconsistency
• security risks
• fraudulent registrations

To maintain:

data integrity

we need to identify duplicate emails.

You are given a Person table.

Table Structure

Column Meaning

Important Note

email is NEVER NULL

This simplifies counting because every row contains a valid email value.

Goal

Find all email addresses that appear:

more than once

Example Input

CREATE TABLE Person (id INTEGER, email TEXT);
INSERT INTO Person VALUES
  (1, 'a@b.com'),
  (2, 'c@d.com'),
  (3, 'a@b.com');

Expected Output

email

a@b.com

Explanation

Email frequency:

Since:

a@b.com appears twice

it is considered duplicate.

Intuition

This is one of the most important SQL interview patterns:

Find duplicates using GROUP BY + HAVING

We need to:

1. group rows by email
2. count occurrences
3. keep only emails appearing more than once

Mental Model

Think of SQL creating buckets:

a@b.com bucket
c@d.com bucket

Now SQL counts:

how many rows exist inside each bucket

Finally:

keep only buckets where count > 1

Approach

Step 1 — Group by Email

We want:

one group per email

So we use:

GROUP BY email

Step 2 — Count Occurrences

Each row represents:

one email registration

So:

COUNT(email)

gives total occurrences of each email.

Step 3 — Filter Duplicates

We only need emails appearing:

more than once

So we use:

HAVING COUNT(email) > 1

Why HAVING Instead of WHERE?

Important SQL rule:

WHERE filters rows
HAVING filters groups

Since:

COUNT(email)

is an aggregated value,

we MUST use:

HAVING

SQL Query

SELECT
    -- Email address
    email

FROM Person

-- Create one group per email
GROUP BY email

-- Keep only duplicate emails
HAVING COUNT(email) > 1;

Internal Execution Flow

Step 1 — GROUP BY Creates Email Buckets

SQL internally creates:

a@b.com → [2 rows]
c@d.com → [1 row]

Step 2 — COUNT(email)

Step 3 — HAVING Filters Results

Condition:

COUNT(email) > 1

Remaining result:

email

a@b.com

Why COUNT(email) Works Here

Because:

email is guaranteed NOT NULL

So every row is counted safely.

Alternative Cleaner Version

Since email is never NULL:

COUNT(*)

also works perfectly.

Equivalent query:

SELECT email
FROM Person
GROUP BY email
HAVING COUNT(*) > 1;

Time Complexity

Time Complexity: O(N)

Where:

• N = total rows in Person table

Why?

• SQL scans all rows once
• groups emails
• counts occurrences

Common Mistakes to Avoid

Mistake 1 — Using WHERE with COUNT

Wrong:

SELECT email
FROM Person
WHERE COUNT(email) > 1
GROUP BY email;

This fails because:

WHERE cannot use aggregate functions

Correct:

HAVING COUNT(email) > 1

Mistake 2 — Forgetting GROUP BY

Wrong:

SELECT email, COUNT(email)
FROM Person;

This causes error because:

email is non-aggregated

Mistake 3 — Using DISTINCT Instead of GROUP BY

Wrong idea:

SELECT DISTINCT email
FROM Person;

This only removes duplicates from output.

It does NOT tell:

which emails are duplicated

Mistake 4 — Using COUNT(DISTINCT email)

Wrong:

HAVING COUNT(DISTINCT email) > 1

Inside one email group:

DISTINCT email is always 1

So this logic is incorrect.

Key SQL Concepts Learned

Final Mental Shortcut

Whenever the question says:

• duplicate emails
• repeated users
• products appearing multiple times
• customers with repeated actions
• duplicate records

Think:

GROUP BY
→ COUNT()
→ HAVING COUNT() > 1