Identify Financial Performance Drivers

Task

You are given monthly financial data and asked to identify the main drivers of performance by business segment. Write a PostgreSQL query that joins the tables, calculates revenue, cost, and gross profit by segment for a given month, and classifies each segment as growth, stable, or decline based on gross profit margin.

Schema

Table	Column	Type	Description
`segments`	`segment_id`	`INT`	Primary key for each business segment
`segments`	`segment_name`	`VARCHAR(100)`	Segment label
`segments`	`region`	`VARCHAR(50)`	Operating region
`financial_facts`	`fact_id`	`INT`	Primary key for each financial record
`financial_facts`	`segment_id`	`INT`	Segment reference
`financial_facts`	`month_end`	`DATE`	Month-end reporting date
`financial_facts`	`revenue`	`NUMERIC(12,2)`	Reported revenue
`financial_facts`	`cost`	`NUMERIC(12,2)`	Reported cost
`financial_facts`	`is_adjusted`	`BOOLEAN`	Whether the record is adjusted

Sample data

segments

segment_id	segment_name	region
1	Direct Lending	North America
2	Asset Recovery	North America
3	Servicing	Europe
4	Capital Markets	North America

financial_facts

fact_id	segment_id	month_end	revenue	cost	is_adjusted
101	1	2024-03-31	120000.00	70000.00	true
102	2	2024-03-31	90000.00	65000.00	true
103	3	2024-03-31	50000.00	48000.00	true
104	4	2024-03-31	150000.00	90000.00	false
105	1	2024-02-29	110000.00	68000.00	true
106	2	2024-02-29	85000.00	64000.00	true
107	3	2024-02-29	52000.00	51000.00	true
108	4	2024-02-29	140000.00	88000.00	true

Expected output

segment_name	region	total_revenue	total_cost	gross_profit	gross_margin	performance_label
Direct Lending	North America	120000.00	70000.00	50000.00	0.4167	growth
Asset Recovery	North America	90000.00	65000.00	25000.00	0.2778	stable
Servicing	Europe	50000.00	48000.00	2000.00	0.0400	decline

Problem

Task

Schema

Table	Column	Type	Description
`segments`	`segment_id`	`INT`	Primary key for each business segment
`segments`	`segment_name`	`VARCHAR(100)`	Segment label
`segments`	`region`	`VARCHAR(50)`	Operating region
`financial_facts`	`fact_id`	`INT`	Primary key for each financial record
`financial_facts`	`segment_id`	`INT`	Segment reference
`financial_facts`	`month_end`	`DATE`	Month-end reporting date
`financial_facts`	`revenue`	`NUMERIC(12,2)`	Reported revenue
`financial_facts`	`cost`	`NUMERIC(12,2)`	Reported cost
`financial_facts`	`is_adjusted`	`BOOLEAN`	Whether the record is adjusted

Sample data

segments

segment_id	segment_name	region
1	Direct Lending	North America
2	Asset Recovery	North America
3	Servicing	Europe
4	Capital Markets	North America

financial_facts

fact_id	segment_id	month_end	revenue	cost	is_adjusted
101	1	2024-03-31	120000.00	70000.00	true
102	2	2024-03-31	90000.00	65000.00	true
103	3	2024-03-31	50000.00	48000.00	true
104	4	2024-03-31	150000.00	90000.00	false
105	1	2024-02-29	110000.00	68000.00	true
106	2	2024-02-29	85000.00	64000.00	true
107	3	2024-02-29	52000.00	51000.00	true
108	4	2024-02-29	140000.00	88000.00	true

Expected output

segment_name	region	total_revenue	total_cost	gross_profit	gross_margin	performance_label
Direct Lending	North America	120000.00	70000.00	50000.00	0.4167	growth
Asset Recovery	North America	90000.00	65000.00	25000.00	0.2778	stable
Servicing	Europe	50000.00	48000.00	2000.00	0.0400	decline

Problem

Task

Schema

Table	Column	Type	Description
`segments`	`segment_id`	`INT`	Primary key for each business segment
`segments`	`segment_name`	`VARCHAR(100)`	Segment label
`segments`	`region`	`VARCHAR(50)`	Operating region
`financial_facts`	`fact_id`	`INT`	Primary key for each financial record
`financial_facts`	`segment_id`	`INT`	Segment reference
`financial_facts`	`month_end`	`DATE`	Month-end reporting date
`financial_facts`	`revenue`	`NUMERIC(12,2)`	Reported revenue
`financial_facts`	`cost`	`NUMERIC(12,2)`	Reported cost
`financial_facts`	`is_adjusted`	`BOOLEAN`	Whether the record is adjusted

Sample data

segments

segment_id	segment_name	region
1	Direct Lending	North America
2	Asset Recovery	North America
3	Servicing	Europe
4	Capital Markets	North America

financial_facts

fact_id	segment_id	month_end	revenue	cost	is_adjusted
101	1	2024-03-31	120000.00	70000.00	true
102	2	2024-03-31	90000.00	65000.00	true
103	3	2024-03-31	50000.00	48000.00	true
104	4	2024-03-31	150000.00	90000.00	false
105	1	2024-02-29	110000.00	68000.00	true
106	2	2024-02-29	85000.00	64000.00	true
107	3	2024-02-29	52000.00	51000.00	true
108	4	2024-02-29	140000.00	88000.00	true

Expected output

segment_name	region	total_revenue	total_cost	gross_profit	gross_margin	performance_label
Direct Lending	North America	120000.00	70000.00	50000.00	0.4167	growth
Asset Recovery	North America	90000.00	65000.00	25000.00	0.2778	stable
Servicing	Europe	50000.00	48000.00	2000.00	0.0400	decline

Problem

Task

Schema

Table	Column	Type	Description
`segments`	`segment_id`	`INT`	Primary key for each business segment
`segments`	`segment_name`	`VARCHAR(100)`	Segment label
`segments`	`region`	`VARCHAR(50)`	Operating region
`financial_facts`	`fact_id`	`INT`	Primary key for each financial record
`financial_facts`	`segment_id`	`INT`	Segment reference
`financial_facts`	`month_end`	`DATE`	Month-end reporting date
`financial_facts`	`revenue`	`NUMERIC(12,2)`	Reported revenue
`financial_facts`	`cost`	`NUMERIC(12,2)`	Reported cost
`financial_facts`	`is_adjusted`	`BOOLEAN`	Whether the record is adjusted

Sample data

segments

segment_id	segment_name	region
1	Direct Lending	North America
2	Asset Recovery	North America
3	Servicing	Europe
4	Capital Markets	North America

financial_facts

fact_id	segment_id	month_end	revenue	cost	is_adjusted
101	1	2024-03-31	120000.00	70000.00	true
102	2	2024-03-31	90000.00	65000.00	true
103	3	2024-03-31	50000.00	48000.00	true
104	4	2024-03-31	150000.00	90000.00	false
105	1	2024-02-29	110000.00	68000.00	true
106	2	2024-02-29	85000.00	64000.00	true
107	3	2024-02-29	52000.00	51000.00	true
108	4	2024-02-29	140000.00	88000.00	true

Expected output

segment_name	region	total_revenue	total_cost	gross_profit	gross_margin	performance_label
Direct Lending	North America	120000.00	70000.00	50000.00	0.4167	growth
Asset Recovery	North America	90000.00	65000.00	25000.00	0.2778	stable
Servicing	Europe	50000.00	48000.00	2000.00	0.0400	decline

Interview Guides

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output

Identify Financial Performance Drivers

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output