Singapore City Pivot with Joins | Dataford Interview Questions

Suppose you have two dataframe df1 and df2 (df1: id, name; df2: id, country, address, city, count). Join them, filter where the country is Singapore, pivot the output, and show the Singapore city with the highest count of people on top.

Asked in the Round 2 stage.

Output

Return one row per Singapore city.
Include the city name and the total people count for that city.
Sort by total people count from highest to lowest, then by city name alphabetically for ties.

Schema

df1

Column	Type	Description
idPK	INT	Unique identifier for the person record
name	VARCHAR(100)	Person name

df2

Column	Type	Description
id	INT	Identifier linking to df1.id
country	VARCHAR(100)	Country name
address	VARCHAR(255)	Street address
city	VARCHAR(100)	City name
count	INT	Number of people associated with the row

Your question is Singapore City Pivot with Joins. 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

Asked in the Round 2 stage.

Output

Return one row per Singapore city.
Include the city name and the total people count for that city.
Sort by total people count from highest to lowest, then by city name alphabetically for ties.

Schema

df1

Column	Type	Description
idPK	INT	Unique identifier for the person record
name	VARCHAR(100)	Person name

df2

Column	Type	Description
id	INT	Identifier linking to df1.id
country	VARCHAR(100)	Country name
address	VARCHAR(255)	Street address
city	VARCHAR(100)	City name
count	INT	Number of people associated with the row

Your question is Singapore City Pivot with Joins. 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.