Free SQL Server Take-Home Assignment Template for Data Engineer Hiring

IF OBJECT_ID('dbo.tickets', 'U') IS NOT NULL DROP TABLE dbo.tickets;
IF OBJECT_ID('dbo.agents', 'U') IS NOT NULL DROP TABLE dbo.agents;
IF OBJECT_ID('dbo.customers', 'U') IS NOT NULL DROP TABLE dbo.customers;

CREATE TABLE dbo.customers (
  customer_id INT PRIMARY KEY,
  company_name NVARCHAR(255) NOT NULL,
  plan_name NVARCHAR(50) NOT NULL
);

CREATE TABLE dbo.agents (
  agent_id INT PRIMARY KEY,
  agent_name NVARCHAR(255) NOT NULL,
  team NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.tickets (
  ticket_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  agent_id INT NOT NULL,
  priority NVARCHAR(20) NOT NULL,
  created_at DATETIME2 NOT NULL,
  first_response_at DATETIME2 NOT NULL,
  resolved_at DATETIME2 NOT NULL,
  reopened BIT NOT NULL DEFAULT 0,
  CONSTRAINT fk_tickets_customer
    FOREIGN KEY (customer_id) REFERENCES dbo.customers(customer_id),
  CONSTRAINT fk_tickets_agent
    FOREIGN KEY (agent_id) REFERENCES dbo.agents(agent_id),
  CONSTRAINT chk_priority
    CHECK (priority IN ('critical', 'high', 'normal'))
);

INSERT INTO dbo.customers (customer_id, company_name, plan_name) VALUES
  (1, 'Northwind Analytics', 'Startup'),
  (2, 'BluePeak Logistics', 'Growth'),
  (3, 'Cedar Bank', 'Enterprise'),
  (4, 'Luma Retail', 'Growth');

INSERT INTO dbo.agents (agent_id, agent_name, team) VALUES
  (10, 'Ava', 'Platform'),
  (11, 'Mihkel', 'Data'),
  (12, 'Sara', 'Data');

INSERT INTO dbo.tickets (
  ticket_id,
  customer_id,
  agent_id,
  priority,
  created_at,
  first_response_at,
  resolved_at,
  reopened
) VALUES
  (1001, 1, 10, 'high',     '2025-01-03T09:00:00', '2025-01-03T13:30:00', '2025-01-04T10:00:00', 0),
  (1002, 2, 11, 'normal',   '2025-01-05T08:00:00', '2025-01-06T10:00:00', '2025-01-07T09:00:00', 1),
  (1003, 3, 12, 'critical', '2025-01-08T14:00:00', '2025-01-08T19:00:00', '2025-01-09T14:00:00', 0),
  (1004, 4, 11, 'high',     '2025-01-10T10:00:00', '2025-01-10T17:00:00', '2025-01-11T12:00:00', 0),
  (1005, 3, 10, 'normal',   '2025-01-12T09:00:00', '2025-01-12T20:00:00', '2025-01-13T09:00:00', 0),
  (1006, 2, 12, 'critical', '2025-01-18T11:00:00', '2025-01-18T16:30:00', '2025-01-19T11:00:00', 1),
  (1007, 1, 10, 'normal',   '2025-02-02T09:00:00', '2025-02-03T08:00:00', '2025-02-04T09:00:00', 0),
  (1008, 2, 11, 'high',     '2025-02-04T12:00:00', '2025-02-04T22:00:00', '2025-02-05T10:00:00', 0),
  (1009, 4, 12, 'normal',   '2025-02-06T09:00:00', '2025-02-07T12:00:00', '2025-02-08T09:00:00', 0),
  (1010, 3, 10, 'critical', '2025-02-07T15:00:00', '2025-02-07T17:00:00', '2025-02-07T23:00:00', 0),
  (1011, 3, 11, 'high',     '2025-02-12T08:00:00', '2025-02-12T18:30:00', '2025-02-13T08:00:00', 1),
  (1012, 2, 12, 'normal',   '2025-02-16T09:00:00', '2025-02-16T20:00:00', '2025-02-17T09:00:00', 0);

SELECT
  CAST(DATEFROMPARTS(YEAR(t.created_at), MONTH(t.created_at), 1) AS date) AS [month],
  c.plan_name AS [plan],
  COUNT(*) AS total_tickets,
  SUM(
    CASE
      WHEN DATEDIFF(MINUTE, t.created_at, t.first_response_at) >
        CASE t.priority
          WHEN 'critical' THEN 4 * 60
          WHEN 'high' THEN 8 * 60
          WHEN 'normal' THEN 24 * 60
        END
      THEN 1
      ELSE 0
    END
  ) AS breached_tickets,
  CAST(
    ROUND(
      SUM(
        CASE
          WHEN DATEDIFF(MINUTE, t.created_at, t.first_response_at) >
            CASE t.priority
              WHEN 'critical' THEN 4 * 60
              WHEN 'high' THEN 8 * 60
              WHEN 'normal' THEN 24 * 60
            END
          THEN 1.0
          ELSE 0.0
        END
      ) * 100.0 / COUNT(*),
      1
    ) AS decimal(5,1)
  ) AS breach_rate_pct
FROM dbo.tickets t
JOIN dbo.customers c
  ON t.customer_id = c.customer_id
GROUP BY
  CAST(DATEFROMPARTS(YEAR(t.created_at), MONTH(t.created_at), 1) AS date),
  c.plan_name
ORDER BY
  [month],
  [plan];

WITH customer_monthly_burden AS (
  SELECT
    CAST(DATEFROMPARTS(YEAR(t.created_at), MONTH(t.created_at), 1) AS date) AS [month],
    c.customer_id,
    c.company_name,
    SUM(
      CASE t.priority
        WHEN 'critical' THEN 5
        WHEN 'high' THEN 3
        WHEN 'normal' THEN 1
      END
    ) AS burden_score
  FROM dbo.tickets t
  JOIN dbo.customers c
    ON t.customer_id = c.customer_id
  GROUP BY
    CAST(DATEFROMPARTS(YEAR(t.created_at), MONTH(t.created_at), 1) AS date),
    c.customer_id,
    c.company_name
),
ranked AS (
  SELECT
    customer_monthly_burden.*,
    ROW_NUMBER() OVER (
      PARTITION BY [month]
      ORDER BY burden_score DESC, customer_id ASC
    ) AS rn
  FROM customer_monthly_burden
)
SELECT
  [month],
  customer_id,
  company_name,
  burden_score
FROM ranked
WHERE rn = 1
ORDER BY [month];