SQL Aggregation With Joins | Dataford Interview Questions

Write an SQL query using JOIN and GROUP BY to aggregate bug reporting data across multiple database tables.

Return one row per product and severity, including the total number of bug reports and the number of distinct reporters. Include only bugs with status other than closed.

Output

One row per product_name and severity
Columns: product_name, severity, bug_report_count, distinct_reporter_count
Sort by bug_report_count descending, then product_name, then severity

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product name

bugs

Column	Type	Description
bug_idPK	INT	Unique bug identifier
product_id	INT	Product associated with the bug
severity	VARCHAR(20)	Bug severity level
status	VARCHAR(20)	Bug status

bug_reports

Column	Type	Description
report_idPK	INT	Unique bug report identifier
bug_id	INT	Reported bug identifier
reporter_id	INT	Identifier of the person who reported the bug
reported_at	DATE	Date the bug was reported

Your question is SQL Aggregation With Joins. 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