Counting Active Transit Lines | Dataford Interview Questions

Moovit maintains transit agency and line metadata and needs an accurate count of currently active lines for a selected agency. Write a PostgreSQL query that returns the agency name and its number of active lines.

Requirements

Filter the result to the agency named Egged.
Count only lines where is_active is TRUE; exclude inactive and NULL status values.
Return the agency name and the count as active_line_count.
Preserve the agency even if it has no active lines by using an appropriate join and conditional aggregation.

Schema

agencies

Column	Type	Description
agency_idPK	INTEGER	Unique transit agency identifier
agency_name	VARCHAR(100)	Transit agency name
country_code	VARCHAR(2)	ISO-style operating country code

transit_lines

Column	Type	Description
line_idPK	INTEGER	Unique transit line identifier
agency_id	INTEGER	Agency operating the line
line_name	VARCHAR(50)	Publicly displayed line name
is_active	BOOLEAN	Whether the line is currently active

Your question is Counting Active Transit Lines. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem