Untitled

//SQL
Q1

SELECT
  f.qh,
  f.forecasted_partners AS forecast,
  a.actual_partners     AS actual,
  a.actual_partners - f.forecasted_partners AS delta
FROM staffing_forecast f
JOIN staffing_actual  a
  ON a.storenumber = f.storenumber
 AND a.dt         = f.dt
 AND a.qh         = f.qh
WHERE f.storenumber = 123
  AND f.dt = DATE '2025-10-05'
ORDER BY f.qh;

Q2

SELECT
  f.storenumber,
  SUM(f.forecasted_partners) AS total_forecast,
  SUM(a.actual_partners)     AS total_actual,
  SUM(ABS(a.actual_partners - f.forecasted_partners)) AS total_abs_error
FROM staffing_forecast f
JOIN staffing_actual  a
  ON a.storenumber = f.storenumber
 AND a.dt         = f.dt
 AND a.qh         = f.qh
WHERE f.dt BETWEEN DATE '2025-10-01' AND DATE '2025-10-07'
GROUP BY f.storenumber
ORDER BY total_abs_error DESC;

Q3

WITH daily_err AS (
  SELECT
    f.storenumber,
    f.dt,
    SUM(ABS(a.actual_partners - f.forecasted_partners)) AS abs_err
  FROM staffing_forecast f
  JOIN staffing_actual a
    ON a.storenumber = f.storenumber
   AND a.dt         = f.dt
   AND a.qh         = f.qh
  WHERE f.dt BETWEEN DATE '2025-10-01' AND DATE '2025-10-07'
  GROUP BY f.storenumber, f.dt
),
ranked AS (
  SELECT
    storenumber,
    dt,
    abs_err,
    RANK() OVER (PARTITION BY storenumber ORDER BY abs_err DESC) AS rnk
  FROM daily_err
)
SELECT storenumber, dt, abs_err
FROM ranked
WHERE rnk <= 3
ORDER BY storenumber, abs_err DESC, dt;

Q4:

WITH daily AS (
  SELECT
    f.storenumber,
    f.dt,
    SUM(a.actual_partners) - SUM(f.forecasted_partners) AS bias
  FROM staffing_forecast f
  JOIN staffing_actual a
    ON a.storenumber = f.storenumber
   AND a.dt         = f.dt
   AND a.qh         = f.qh
  WHERE f.dt BETWEEN DATE '2025-10-01' AND DATE '2025-10-14'
  GROUP BY f.storenumber, f.dt
)
SELECT
  storenumber,
  dt AS day,
  bias,
  AVG(bias) OVER (
    PARTITION BY storenumber
    ORDER BY dt
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS roll3_bias
FROM daily
ORDER BY storenumber, day;

Q5:

WITH daily AS (
  SELECT
    f.storenumber,
    f.dt,
    ABS(SUM(a.actual_partners) - SUM(f.forecasted_partners)) * 1.0
      / NULLIF(SUM(a.actual_partners), 0) AS mape
  FROM staffing_forecast f
  JOIN staffing_actual a
    ON a.storenumber = f.storenumber
   AND a.dt         = f.dt
   AND a.qh         = f.qh
  WHERE f.dt BETWEEN DATE '2025-10-01' AND DATE '2025-10-14'
  GROUP BY f.storenumber, f.dt
),
pick AS (
  SELECT
    storenumber,
    dt,
    mape,
    ROW_NUMBER() OVER (PARTITION BY storenumber ORDER BY mape DESC) AS rnum
  FROM daily
)
SELECT
  storenumber,
  dt AS worst_day,
  ROUND(mape * 100, 2) AS mape_pct
FROM pick
WHERE rnum = 1
ORDER BY mape_pct DESC;



Python 

Q1:

def carry_forward_schedule(demand, limit):
    n = len(demand)
    schedule = [0] * n
    carry = 0
    for i in range(n):
        need = demand[i] + carry
        take = min(need, limit[i])
        schedule[i] = take
        carry = need - take
    return schedule, carry

# quick check
sched, left = carry_forward_schedule([3,0,4,2], [2,2,2,2])
assert sched == [2,1,2,2] and left == 2

Q2:

import pandas as pd
import numpy as np

def carry_forward_pandas(df):
    # df columns: qh, demand, limit. qh must be sorted
    df = df.sort_values("qh").reset_index(drop=True)

    df["cum_demand"] = df["demand"].cumsum()
    df["cum_capacity"] = df["limit"].cumsum()

    df["served_cum"] = np.minimum(df["cum_demand"], df["cum_capacity"])
    df["served"] = df["served_cum"].diff().fillna(df["served_cum"])

    df["carry_out"] = df["cum_demand"] - df["served_cum"]
    leftover = int(df["carry_out"].iloc[-1])

    return df[["qh", "demand", "limit", "served"]], leftover

# quick check
test = pd.DataFrame({
    "qh": [0,1,2,3],
    "demand": [3,0,4,2],
    "limit":  [2,2,2,2]
})
out, left = carry_forward_pandas(test)
assert list(out["served"]) == [2,1,2,2] and left == 2