Quality Control Dataset for Production Analysis

• quality_checks.run_id →references production_data.run_idmany to one: each Quality checks row points to one Production data row
• production_data.operator_id →references operators.operator_idmany to one: each Production data row points to one Operators row
• production_data.machine_id →references machines.machine_idmany to one: each Production data row points to one Machines row

1. What are the most common defect codes observed in quality checks, and which production runs are most affected?

   tables: quality_checks, production_data

2. Identify operators or machines associated with a higher incidence of defects (Fail result).

   tables: quality_checks, operators, machines

3. Analyze the trend of defect rates over time for specific product IDs.

   tables: quality_checks, production_data

4. Calculate the overall pass rate for each check type.

   tables: quality_checks

Table names match the SQLite file and the SQL script.

SELECT defect_code, COUNT(*) AS defect_count FROM quality_checks WHERE result = 'Fail' AND defect_code IS NOT NULL GROUP BY defect_code ORDER BY defect_count DESC LIMIT 20;

SELECT run_id, COUNT(*) AS failure_count FROM quality_checks WHERE result = 'Fail' GROUP BY run_id ORDER BY failure_count DESC LIMIT 20;

SELECT m.machine_model, SUM(CASE WHEN qc.result = 'Fail' THEN 1 ELSE 0 END) * 100.0 / COUNT(qc.check_id) AS defect_rate FROM quality_checks qc JOIN production_data pd ON qc.run_id = pd.run_id JOIN machines m ON pd.machine_id = m.machine_id GROUP BY m.machine_model ORDER BY defect_rate DESC LIMIT 20;

SELECT shift_type, AVG(batch_quantity) AS avg_batch_quantity FROM production_data GROUP BY shift_type LIMIT 20;

SELECT check_type, SUM(CASE WHEN result = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pass_rate FROM quality_checks GROUP BY check_type LIMIT 20;

Excel

Load each table into a separate sheet. Use XLOOKUP to join tables based on common IDs (e.g., operator_id, machine_id, run_id). Create a PivotTable on the quality_checks sheet to analyze results by check_type or defect_code.

Power BI

Model the data using a star schema: 'production_data' as the fact table, with 'operators', 'machines', and 'quality_checks' as dimension tables. Create relationships based on the respective IDs. DAX measures could include: Total Checks = COUNT(quality_checks[check_id]), Failures = CALCULATE(COUNT(quality_checks[check_id]), quality_checks[result] = "Fail"), Pass Rate = DIVIDE([Failures], [Total Checks]).

SQL

Load the SQLite or SQL relational download. Use the specified keys and join paths (e.g., quality_checks.run_id to production_data.run_id) to perform relational queries. Ensure foreign key constraints are maintained if using a relational database.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file