Process Data File With SQL | Dataford Interview Questions

Write a program or query that processes a data file and produces analysis of the data.

Assume the file has been loaded into the tables below. Analyze records by file and category, excluding files that were not successfully loaded and categories with fewer than two records.

Output

One row per qualifying file and category, with file_name, category, record_count, valid_count, total_amount, average_amount, and valid_pct.
Include NULL categories when they qualify. Sort by file_name ascending, then category ascending, with NULLs last.

Schema

data_files

Column	Type	Description
file_idPK	INT	Unique identifier for the loaded file
file_name	VARCHAR(255)	Original name of the data file
load_status	VARCHAR(30)	Result of loading the file

data_rows

Column	Type	Description
row_idPK	INT	Unique identifier for the imported row
file_id	INT	Identifier of the source file
category	VARCHAR(100)	Category recorded in the data file
amount	DECIMAL(12,2)	Optional numeric value for the row
validation_status	VARCHAR(30)	Validation result for the row

Your question is Process Data File With SQL. Start with the requirements and the two 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