Microsoft Role Understanding Questions

-- SQL pseudocode: prior quarter revenue and avg latency per region
WITH quarter_dates AS (
  SELECT
    DATEADD(quarter, DATEDIFF(quarter, '2000-01-01', CURRENT_DATE()) - 1, '2000-01-01') AS q_start,
    DATEADD(day, -1, DATEADD(quarter, DATEDIFF(quarter, '2000-01-01', CURRENT_DATE()), '2000-01-01')) AS q_end
),
region_revenue AS (
  SELECT
    r.region_name,
    SUM(b.amount) AS revenue
  FROM billing.transactions b
  JOIN accounts a ON b.account_id = a.id
  JOIN regions r ON a.region_id = r.id
  CROSS JOIN quarter_dates q
  WHERE b.invoice_date BETWEEN q.q_start AND q.q_end
  GROUP BY r.region_name
),
region_latency AS (
  SELECT
    m.region_name,
    AVG(m.latency_ms) AS avg_latency_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY m.latency_ms) AS p95_latency_ms
  FROM monitoring.metrics m
  CROSS JOIN quarter_dates q
  WHERE m.timestamp BETWEEN q.q_start AND q.q_end
  GROUP BY m.region_name
)
SELECT
  rr.region_name,
  rr.revenue,
  rl.avg_latency_ms,
  rl.p95_latency_ms
FROM region_revenue rr
LEFT JOIN region_latency rl ON rr.region_name = rl.region_name
ORDER BY rr.revenue DESC;

-- Assumes table DailyCosts with columns: Date, SubscriptionId, Cost, Tag_CostCenter
CostPerSubscriptionByTag =
SUMMARIZE(
    FILTER( DailyCosts, DailyCosts[Date] >= STARTOFMONTH(TODAY()) ), -- month-to-date
    DailyCosts[Tag_CostCenter],
    DailyCosts[SubscriptionId],
    "TotalCost", SUM(DailyCosts[Cost])
)

TotalCost_MTD = 
CALCULATE(
    SUM(DailyCosts[Cost]),
    DATESBETWEEN(DailyCosts[Date], STARTOFMONTH(TODAY()), TODAY())
)

CostPerSubscription_MTD = 
DIVIDE(
    [TotalCost_MTD],
    DISTINCTCOUNT(DailyCosts[SubscriptionId])
)

SELECT
  tag_value AS cost_center,
  subscription_id,
  SUM(cost) AS total_cost_mtd
FROM daily_costs
WHERE date >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND date <= CAST(GETDATE() AS date)
GROUP BY tag_value, subscription_id;