Flatten Nested Data in SQL | Dataford Interview Questions

Write a SQL query to flatten hierarchical or nested data into a tabular format.

Assume the input is represented by divisions, teams, and members, where teams belong to divisions and members may belong to teams. Preserve divisions and teams even when their child rows are absent.

Output

One row per team-member combination, plus rows for divisions or teams without children.
Return division_id, division_name, team_id, team_name, member_id, member_name, and member_role.
Sort by division, team, and member identifiers, placing missing child rows last.

Schema

divisions

Column	Type	Description
division_idPK	INT	Unique division identifier
division_name	VARCHAR(100)	Division name

teams

Column	Type	Description
team_idPK	INT	Unique team identifier
division_id	INT	Parent division identifier
team_name	VARCHAR(100)	Team name

members

Column	Type	Description
member_idPK	INT	Unique member identifier
team_id	INT	Parent team identifier
member_name	VARCHAR(100)	Member name
member_role	VARCHAR(100)	Member role

Your question is Flatten Nested Data in SQL. Start with the requirements and the three 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