Katkat
Back

Write SQL Data Transformation Queries

TeslaData Engineer · Technical Screen
MediumData EngineerTechnical ScreenCoding & Algorithms

Last updated

This question tests the ability to handle window functions for running totals and conditional aggregation to transform raw manufacturing logs into formatted monthly production reports.

Problem Statement

You are given a table manufacturing_logs that tracks daily output for various assembly lines. You need to generate a summary report that shows the cumulative production for each line per month, while also calculating the percentage of production that was flagged as 'defective'.

Schema: manufacturing_logs

ColumnTypeDescription
log_idINTUnique identifier for the log entry
line_idINTIdentifier for the assembly line
production_dateDATEDate of production
units_producedINTTotal units manufactured on this date
units_defectiveINTUnits flagged as defective on this date

Task

Write a SQL query to return a table with the following columns: line_id, month (in YYYY-MM format), total_produced, cumulative_monthly_production, and defect_rate.

  • total_produced: Total units produced by the line in that specific month.
  • cumulative_monthly_production: The running total of total_produced for that specific line_id, ordered by month.
  • defect_rate: The ratio of total defective units to total produced units for that month, expressed as a percentage.

Example Input

log_idline_idproduction_dateunits_producedunits_defective
11012023-01-051005
21012023-01-2020010
31012023-02-101500

Example Output

line_idmonthtotal_producedcumulative_monthly_productiondefect_rate
1012023-013003005.0
1012023-021504500.0

Constraints

  • production_date ranges across multiple years.
  • Assume units_produced > 0 for all rows.

Hints

Hint 1

First, group the data by line and month to calculate the base totals for each month before applying any window functions.

Hint 2

Use the `SUM(total_produced) OVER (PARTITION BY line_id ORDER BY month)` window function to calculate the running total.

Hint 3

Ensure you cast your division to a decimal/float type when calculating the defect rate to avoid integer division truncation.

Follow-up questions

  • How would you modify this query to only include lines that had a defect rate exceeding 5% in any given month?
  • If the table grew to billions of rows, what indexes would you suggest creating to optimize this aggregation?
  • How would you handle a scenario where a `line_id` reports data in different time zones, potentially causing date shifts?