Most Data Analytics Applied | Dataford Interview Questions

Tell me about the project where you applied the most data analytics, and what impact it had.

Using the provided project, analysis-run, and impact data, write a PostgreSQL query that identifies the project with the greatest completed analytics activity while reporting impact for every project.

Output

One row per project with project_id, project_name, completed_analysis_count, and total_impact
Include projects without analysis runs or impact records, treating missing totals as zero
Order by completed analysis count descending, total impact descending, and project ID ascending

Schema

projects

Column	Type	Description
project_idPK	INT	Unique project identifier
project_name	VARCHAR(100)	Project name
owner_team	VARCHAR(100)	Team responsible for the project
completed_date	DATE	Date the project was completed

analysis_runs

Column	Type	Description
run_idPK	INT	Unique analysis run identifier
project_id	INT	Project associated with the analysis run
run_date	DATE	Date the analysis run occurred
status	VARCHAR(20)	Analysis run status

project_impacts

Column	Type	Description
impact_idPK	INT	Unique impact record identifier
project_id	INT	Project associated with the impact
impact_date	DATE	Date the impact was recorded
impact_type	VARCHAR(40)	Type of measured impact
impact_value	DECIMAL(12,2)	Numeric value of the impact

Your question is Most Data Analytics Applied. 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