Active Headcount per Department

SQL practice question · Easy · Joins · Accenture, TCS

Count employees with status 'ACTIVE' in every department; departments with no active employees must still appear with 0. Employees without a department are ignored. Return dept_name and active_headcount. Order by active_headcount DESC, then dept_name.

Table: departments

Schema of departments
Column	Type
dept_id	INTEGER
dept_name	TEXT

Schema of departments

Column

Type

dept_id

INTEGER

dept_name

TEXT

Sample rows of departments
dept_id	dept_name
1	Cloud
2	Data & AI
3	Security
4	Finance Ops
5	Consulting

Sample rows of departments

dept_id

dept_name

Cloud

Data & AI

Security

Finance Ops

Consulting

Table: employees

Schema of employees
Column	Type
emp_id	INTEGER
emp_name	TEXT
dept_id	INTEGER
status	TEXT

Schema of employees

Column

Type

emp_id

INTEGER

emp_name

TEXT

dept_id

INTEGER

status

TEXT

Sample rows of employees
emp_id	emp_name	dept_id	status
1	Aarav	1	ACTIVE
2	Bela	1	ACTIVE
3	Chirag	2	ACTIVE
4	Dia	2	EXITED
5	Ehsaan	1	EXITED
6	Farah	5	ACTIVE

Sample rows of employees

emp_id

emp_name

dept_id

status

Aarav

ACTIVE

Bela

ACTIVE

Chirag

ACTIVE

Dia

EXITED

Ehsaan

EXITED

Farah

ACTIVE

Expected output on the sample data
dept_name	active_headcount
Cloud	2
Consulting	1
Data & AI	1
Finance Ops	0
Security	0

Expected output on the sample data

dept_name

active_headcount

Cloud

Consulting

Data & AI

Finance Ops

Security

Solve it in the live editor on this page — run on sample data, then submit against hidden tests.

Tech Town

Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Question Asked in Interview Blog

SQL Interview Practice

Company-wise SQL questions

{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.

🏆 Weekly SQL Contest

Questions

Companies

Solved

Bookmark Question Companies Difficulty Category Status

{{ r.n }} - {{ r.title }}

No questions match these filters.

Showing {{ rangeLabel }} of {{ filteredCount }}

Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.

Daily challenge

{{ daily.title }} · {{ daily.difficulty }} Solve now

New question every day at midnight IST.

Your Progress

Reset all SQL practice progress{{ resetScope }}? This can't be undone.

{{ p.label }} ({{ p.pct }}%){{ p.solved }}/{{ p.total }}

Points: {{ myPoints }} · Easy 10 · Medium 20 · Hard 40

🔥 Longest streak: {{ streak.longest }}Current streak: {{ streak.current }}

Leaderboard (rolling 100 days)

Loading…

{{ u.rank }} {{ u.name }}{{ u.points }} pts

← All questions

{{ cur.n }} - {{ cur.title }}

🏆 Contest {{ contestWeek }}

Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide

{{ embedCode }}

Table: {{ tb.name }}

ColumnType

Expected output on the sample data:

No submissions yet. Press Evaluate to submit your query.

The solution unlocks after you solve this question or after 3 evaluations that didn't pass ({{ failCount }}/3 so far). Trying first is the fastest way to learn.

{{ solutionSql }}

SQL dialect ⏱ {{ timerLabel }}

{{ fnHelpTitle }}
{{ fnHelpText }}

Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically

Output

{{ out.error }}

Expected (sample data)

Your output (sample data)

Run your query to see the result here.

🔒

To Track Your Progress, Log In / Sign Up

You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.