Counting Active Transit Lines

EasySQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Counting Active Transit Lines. Start with the requirements and the two tables on the right.

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

You need to log in / sign up to run or submit.

Problem

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

Tablesagenciestransit_lines

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results