Data Analyst Interview Questions & Answers: Set 10, Garments (RMG) Manufacturing

SQL

1. 1.Find production lines whose efficiency was below 70% for 3 days in a row.

   Hide answer

   Answer: Use window functions to look back two rows per line and keep rows where today and the previous two days are all below 70%. Table: line_daily(line_id, prod_date, efficiency_pct), one row per line per working day. The date check makes sure the three rows are truly consecutive calendar days; if Fridays are off and you mean working days, drop that condition. Trap: without the date check, a gap (holiday, no data) would wrongly count as a streak. For longer streaks, use the gaps-and-islands method instead.

   WITH w AS (
     SELECT line_id, prod_date, efficiency_pct,
            LAG(efficiency_pct, 1) OVER p AS prev1_eff,
            LAG(efficiency_pct, 2) OVER p AS prev2_eff,
            LAG(prod_date, 2)      OVER p AS prev2_date
     FROM line_daily
     WINDOW p AS (PARTITION BY line_id ORDER BY prod_date)
   )
   SELECT DISTINCT line_id
   FROM w
   WHERE efficiency_pct < 70
     AND prev1_eff < 70
     AND prev2_eff < 70
     AND prod_date - prev2_date = 2;

2. 2.Calculate the defect rate per line, per shift.

   Hide answer

   Answer: Defect rate = defective pieces / pieces checked x 100, grouped by line and shift. Table: qc_inspection(line_id, shift, insp_date, checked_pcs, defective_pcs). Two traps: 1) compute the ratio of sums, not the average of daily rates, so big and small days are weighted correctly; 2) multiply by 100.0 to avoid integer division. NULLIF prevents divide-by-zero when nothing was checked.

   SELECT line_id,
          shift,
          SUM(checked_pcs)   AS checked,
          SUM(defective_pcs) AS defective,
          ROUND(100.0 * SUM(defective_pcs)
                / NULLIF(SUM(checked_pcs), 0), 2) AS defect_rate_pct
   FROM qc_inspection
   GROUP BY line_id, shift
   ORDER BY line_id, shift;