Interview Guides

Department Cost Totals by Join | Dataford Interview Questions - Dataford - Ace your Interview

Department Cost Totals by Join

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup By

Also asked at

Problem

Task

At Northstar Analytics, finance tracks department spending in a separate expense table. Write a SQL query to join the department and expense data and return the total cost for each department.

Requirements

Join departments and expenses using the department key.
Return each department's name and the sum of its expense cost values.
Group the results by department name.
Order the output by total cost descending, then department name ascending.

Table Definitions

`departments`

column_name	type	description
department_id	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

`expenses`

column_name	type	description
expense_id	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

Sample Data

`departments`

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central

`expenses`

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00

Expected Output

department_name	total_cost
Engineering	3500.00
Marketing	1200.00
Sales	800.00
Legal	0.00

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

expenses

Column	Type	Description
expense_idPK	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

expenses

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00
1003	30	Hardware	1500.00
1005	40	Recruiting	0.00
1007	60	Audit	-100.00
1008	90	Facilities	700.00
1009		Misc	250.00
1010	20	Ads
1011	20	Consulting	997
1012	20	Hardware	1984
1013	90	Payroll	552
1014	90	Maintenance	-421
1015	10	Travel	null
1016	30	Consulting	438
1017	10	Cloud	774
1018	30	Office Supplies	1856
1019	90	Research	-506
1020	20	null	1013
1021	20	Equipment	173
1022	40	Subscriptions	1439
1023	20	Misc	-75
1024	30	null	449
1025	30	Maintenance	1363

departments

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central
70	Support
40	HR	West
60	Finance	Central
80	Operations	South
81	Revenue Operations	Japan
82	Support	null
83	Facilities	Germany
84	Strategy	UK
85	QA	India
86	Training	International
87	Engineering	East
88	Sales	Domestic
89	Research	Germany
90	Operations	India
91	Admin	Canada
92	Revenue Operations	Canada
93	Procurement	India
94	Analytics	North
95	HR	International
96	Operations	Japan
97	Compliance	North

Expected Output

department_name	total_cost
Engineering	7606.00
Marketing	5292.00
Sales	1574.00
HR	1439.00
Operations	325.00
Admin	0.00
Analytics	0.00
Compliance	0.00
Facilities	0.00
Legal	0.00
Procurement	0.00
QA	0.00
Research	0.00
Revenue Operations	0.00
Strategy	0.00
Support	0.00
Training	0.00
Finance	-100.00

Problem

Task

At Northstar Analytics, finance tracks department spending in a separate expense table. Write a SQL query to join the department and expense data and return the total cost for each department.

Requirements

Join departments and expenses using the department key.
Return each department's name and the sum of its expense cost values.
Group the results by department name.
Order the output by total cost descending, then department name ascending.

Table Definitions

`departments`

column_name	type	description
department_id	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

`expenses`

column_name	type	description
expense_id	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

Sample Data

`departments`

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central

`expenses`

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00

Expected Output

department_name	total_cost
Engineering	3500.00
Marketing	1200.00
Sales	800.00
Legal	0.00

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

expenses

Column	Type	Description
expense_idPK	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

expenses

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00
1003	30	Hardware	1500.00
1005	40	Recruiting	0.00
1007	60	Audit	-100.00
1008	90	Facilities	700.00
1009		Misc	250.00
1010	20	Ads
1011	20	Consulting	997
1012	20	Hardware	1984
1013	90	Payroll	552
1014	90	Maintenance	-421
1015	10	Travel	null
1016	30	Consulting	438
1017	10	Cloud	774
1018	30	Office Supplies	1856
1019	90	Research	-506
1020	20	null	1013
1021	20	Equipment	173
1022	40	Subscriptions	1439
1023	20	Misc	-75
1024	30	null	449
1025	30	Maintenance	1363

departments

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central
70	Support
40	HR	West
60	Finance	Central
80	Operations	South
81	Revenue Operations	Japan
82	Support	null
83	Facilities	Germany
84	Strategy	UK
85	QA	India
86	Training	International
87	Engineering	East
88	Sales	Domestic
89	Research	Germany
90	Operations	India
91	Admin	Canada
92	Revenue Operations	Canada
93	Procurement	India
94	Analytics	North
95	HR	International
96	Operations	Japan
97	Compliance	North

Expected Output

department_name	total_cost
Engineering	7606.00
Marketing	5292.00
Sales	1574.00
HR	1439.00
Operations	325.00
Admin	0.00
Analytics	0.00
Compliance	0.00
Facilities	0.00
Legal	0.00
Procurement	0.00
QA	0.00
Research	0.00
Revenue Operations	0.00
Strategy	0.00
Support	0.00
Training	0.00
Finance	-100.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Budget vs Actual Variance ReportEasy Analyze Employee Turnover Trends for Workforce PlanningMedium

Employee Attrition Report by DepartmentMedium

Next question

Department Cost Totals by Join

Easy

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup By

Also asked at

Problem

Task

At Northstar Analytics, finance tracks department spending in a separate expense table. Write a SQL query to join the department and expense data and return the total cost for each department.

Requirements

Join departments and expenses using the department key.
Return each department's name and the sum of its expense cost values.
Group the results by department name.
Order the output by total cost descending, then department name ascending.

Table Definitions

`departments`

column_name	type	description
department_id	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

`expenses`

column_name	type	description
expense_id	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

Sample Data

`departments`

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central

`expenses`

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00

Expected Output

department_name	total_cost
Engineering	3500.00
Marketing	1200.00
Sales	800.00
Legal	0.00

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

expenses

Column	Type	Description
expense_idPK	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

expenses

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00
1003	30	Hardware	1500.00
1005	40	Recruiting	0.00
1007	60	Audit	-100.00
1008	90	Facilities	700.00
1009		Misc	250.00
1010	20	Ads
1011	20	Consulting	997
1012	20	Hardware	1984
1013	90	Payroll	552
1014	90	Maintenance	-421
1015	10	Travel	null
1016	30	Consulting	438
1017	10	Cloud	774
1018	30	Office Supplies	1856
1019	90	Research	-506
1020	20	null	1013
1021	20	Equipment	173
1022	40	Subscriptions	1439
1023	20	Misc	-75
1024	30	null	449
1025	30	Maintenance	1363

departments

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central
70	Support
40	HR	West
60	Finance	Central
80	Operations	South
81	Revenue Operations	Japan
82	Support	null
83	Facilities	Germany
84	Strategy	UK
85	QA	India
86	Training	International
87	Engineering	East
88	Sales	Domestic
89	Research	Germany
90	Operations	India
91	Admin	Canada
92	Revenue Operations	Canada
93	Procurement	India
94	Analytics	North
95	HR	International
96	Operations	Japan
97	Compliance	North

Expected Output

department_name	total_cost
Engineering	7606.00
Marketing	5292.00
Sales	1574.00
HR	1439.00
Operations	325.00
Admin	0.00
Analytics	0.00
Compliance	0.00
Facilities	0.00
Legal	0.00
Procurement	0.00
QA	0.00
Research	0.00
Revenue Operations	0.00
Strategy	0.00
Support	0.00
Training	0.00
Finance	-100.00

Problem

Task

At Northstar Analytics, finance tracks department spending in a separate expense table. Write a SQL query to join the department and expense data and return the total cost for each department.

Requirements

Join departments and expenses using the department key.
Return each department's name and the sum of its expense cost values.
Group the results by department name.
Order the output by total cost descending, then department name ascending.

Table Definitions

`departments`

column_name	type	description
department_id	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

`expenses`

column_name	type	description
expense_id	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

Sample Data

`departments`

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central

`expenses`

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00

Expected Output

department_name	total_cost
Engineering	3500.00
Marketing	1200.00
Sales	800.00
Legal	0.00

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
region	VARCHAR(50)	Region assigned to the department

expenses

Column	Type	Description
expense_idPK	INT	Unique expense identifier
department_id	INT	Department linked to the expense
expense_type	VARCHAR(50)	Category of expense
cost	DECIMAL(10,2)	Expense amount

expenses

expense_id	department_id	expense_type	cost
1004	20	Events	1200.00
1001	10	Travel	500.00
1002	10	Software	300.00
1006	30	Cloud	2000.00
1003	30	Hardware	1500.00
1005	40	Recruiting	0.00
1007	60	Audit	-100.00
1008	90	Facilities	700.00
1009		Misc	250.00
1010	20	Ads
1011	20	Consulting	997
1012	20	Hardware	1984
1013	90	Payroll	552
1014	90	Maintenance	-421
1015	10	Travel	null
1016	30	Consulting	438
1017	10	Cloud	774
1018	30	Office Supplies	1856
1019	90	Research	-506
1020	20	null	1013
1021	20	Equipment	173
1022	40	Subscriptions	1439
1023	20	Misc	-75
1024	30	null	449
1025	30	Maintenance	1363

departments

department_id	department_name	region
30	Engineering	West
10	Sales	East
50	Legal	East
20	Marketing	Central
70	Support
40	HR	West
60	Finance	Central
80	Operations	South
81	Revenue Operations	Japan
82	Support	null
83	Facilities	Germany
84	Strategy	UK
85	QA	India
86	Training	International
87	Engineering	East
88	Sales	Domestic
89	Research	Germany
90	Operations	India
91	Admin	Canada
92	Revenue Operations	Canada
93	Procurement	India
94	Analytics	North
95	HR	International
96	Operations	Japan
97	Compliance	North

Expected Output

department_name	total_cost
Engineering	7606.00
Marketing	5292.00
Sales	1574.00
HR	1439.00
Operations	325.00
Admin	0.00
Analytics	0.00
Compliance	0.00
Facilities	0.00
Legal	0.00
Procurement	0.00
QA	0.00
Research	0.00
Revenue Operations	0.00
Strategy	0.00
Support	0.00
Training	0.00
Finance	-100.00

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Budget vs Actual Variance ReportEasy Analyze Employee Turnover Trends for Workforce PlanningMedium

Employee Attrition Report by DepartmentMedium

Next question