-- Aurelia Cloud — starter queries
-- NavAIgate Build Challenge 01: The Insight Engine
--
-- Written to run against the bundled SQLite database (aurelia.sqlite),
-- where dates are ISO text, so substr(date, 1, 7) gives the month.
-- On Snowflake, swap substr(...) for TO_CHAR(date, 'YYYY-MM') or
-- DATE_TRUNC('month', date). Treat 2026-06-12 as "today".

-- 1. ARR by region and segment
SELECT region, segment, COUNT(*) AS accounts, SUM(arr_usd) AS arr
FROM accounts
GROUP BY region, segment
ORDER BY arr DESC;

-- 2. Monthly active usage by suite
SELECT substr(u.date, 1, 7) AS month, p.suite,
       SUM(u.active_users) AS active_user_days
FROM usage_daily u
JOIN products p ON p.product_id = u.product_id
GROUP BY month, p.suite
ORDER BY month, p.suite;

-- 3. Top 15 accounts by ARR, with health
SELECT account_id, name, segment, region, arr_usd, health_score, renewal_date
FROM accounts
ORDER BY arr_usd DESC
LIMIT 15;

-- 4. Ticket volume and average CSAT per month for Enterprise accounts
--    (worth watching how these two move against each other)
SELECT substr(t.opened_date, 1, 7) AS month,
       COUNT(*) AS tickets, ROUND(AVG(t.csat_score), 2) AS avg_csat
FROM support_tickets t
JOIN accounts a ON a.account_id = t.account_id
WHERE a.segment = 'Enterprise'
GROUP BY month
ORDER BY month;

-- 5. Licensed seats versus recent average daily active users per
--    account-product (interesting where the ratio is unusual)
SELECT u.account_id, u.product_id, MAX(s.seats) AS seats,
       ROUND(AVG(u.active_users), 1) AS avg_daily_active
FROM usage_daily u
JOIN subscriptions s
  ON s.account_id = u.account_id AND s.product_id = u.product_id
 AND s.end_date > '2026-06-12'
WHERE u.date >= '2026-03-14'
GROUP BY u.account_id, u.product_id
ORDER BY avg_daily_active * 1.0 / MAX(s.seats) DESC
LIMIT 25;

-- 6. New-business opportunities created per month, by region
SELECT substr(o.created_date, 1, 7) AS month, a.region, COUNT(*) AS opps
FROM opportunities o
JOIN accounts a ON a.account_id = o.account_id
WHERE o.type = 'new'
GROUP BY month, a.region
ORDER BY month, a.region;

-- 7. Adoption of the newest product since launch
SELECT substr(u.date, 1, 7) AS month,
       COUNT(DISTINCT u.account_id) AS accounts_using,
       SUM(u.active_users) AS active_user_days
FROM usage_daily u
JOIN products p ON p.product_id = u.product_id
WHERE p.launch_date = (SELECT MAX(launch_date) FROM products)
GROUP BY month
ORDER BY month;

-- 8. Month-on-month tokens_consumed by account: biggest single-month jumps
WITH monthly AS (
  SELECT account_id, substr(date, 1, 7) AS month,
         SUM(tokens_consumed) AS tokens
  FROM usage_daily
  GROUP BY account_id, month
)
SELECT m1.account_id, m1.month, m1.tokens,
       ROUND(m1.tokens * 1.0 / NULLIF(m0.tokens, 0), 1) AS vs_prev_month
FROM monthly m1
JOIN monthly m0 ON m0.account_id = m1.account_id
 AND m0.month = strftime('%Y-%m', date(m1.month || '-01', '-1 month'))
ORDER BY vs_prev_month DESC
LIMIT 10;

-- 9. Company-wide usage by calendar month (look for rhythm)
SELECT substr(date, 1, 7) AS month, SUM(active_users) AS active_user_days
FROM usage_daily
GROUP BY month
ORDER BY month;

-- 10. NPS by quarter and segment
SELECT substr(n.date, 1, 4) || '-Q' ||
       ((CAST(substr(n.date, 6, 2) AS INTEGER) + 2) / 3) AS quarter,
       a.segment,
       COUNT(*) AS responses,
       ROUND(100.0 * SUM(CASE WHEN n.score >= 9 THEN 1 ELSE 0 END) / COUNT(*)
             - 100.0 * SUM(CASE WHEN n.score <= 6 THEN 1 ELSE 0 END)
               / COUNT(*), 1) AS nps
FROM nps_responses n
JOIN accounts a ON a.account_id = n.account_id
GROUP BY quarter, a.segment
ORDER BY quarter, a.segment;
