SQL for Duplicate Blueprint Uploads | Dataford Interview Questions

Write a SQL query to identify duplicate blueprint uploads based on matching metadata and file size thresholds.

Use the provided upload and project records. Treat uploads as duplicates when they belong to the same project, have the same blueprint name and non-null metadata hash, and their positive file sizes differ by no more than 5% of the smaller file.

Output

One row per duplicate upload pair.
Return project_name, metadata_hash, upload_id_1, upload_id_2, file_size_1, file_size_2, and size_difference_pct.
Use each pair only once, exclude invalid or unmatched records, and order by project_name, upload_id_1, and upload_id_2.

Schema

projects

Column	Type	Description
project_idPK	INT	Unique project identifier
project_name	VARCHAR(100)	Project display name

blueprint_uploads

Column	Type	Description
upload_idPK	INT	Unique upload identifier
project_id	INT	Project associated with the upload
blueprint_name	VARCHAR(150)	Blueprint file name or logical name
metadata_hash	VARCHAR(64)	Hash of normalized blueprint metadata
file_size_bytes	BIGINT	Uploaded file size in bytes
uploaded_at	TIMESTAMP	Upload timestamp

Your question is SQL for Duplicate Blueprint Uploads. 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