-- Fireblaze SQL interview answer key (SQLite syntax, tested on SQLite 3.53)
-- Load fireblaze_sql_practice.sql first. MySQL/PostgreSQL date syntax is in the article.

-- second_highest
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- nth_dense
SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees WHERE salary IS NOT NULL) t
WHERE rnk = 3;

-- dupes
SELECT customer_id, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;

-- delete_dupes
DELETE FROM orders
WHERE order_id NOT IN (
  SELECT MIN(order_id) FROM orders
  GROUP BY customer_id, order_date, amount);

-- more_than_mgr
SELECT e.emp_name, e.salary, m.emp_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

-- top_per_dept
SELECT dept_id, emp_name, salary FROM (
  SELECT dept_id, emp_name, salary,
         RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees WHERE dept_id IS NOT NULL) t
WHERE rnk = 1;

-- ranks
SELECT emp_name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE dept_id = 10;

-- empty_dept
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

-- no_orders
SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- running_total
SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total
FROM orders
WHERE customer_id = 101;

-- top2_per_customer
SELECT customer_id, order_id, amount FROM (
  SELECT customer_id, order_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders WHERE status = 'Delivered') t
WHERE rn <= 2
ORDER BY customer_id, rn;

-- mom
WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
  FROM orders WHERE status = 'Delivered'
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly;

-- dept_avg_having
SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING AVG(salary) > 60000;

-- counts
SELECT COUNT(*) AS all_rows, COUNT(salary) AS with_salary,
       COUNT(DISTINCT salary) AS distinct_salaries
FROM employees;

-- notin_null
SELECT emp_name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees);

-- notin_fixed
SELECT emp_name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

-- salary_band
SELECT
  CASE WHEN salary >= 100000 THEN 'High'
       WHEN salary >= 50000  THEN 'Mid'
       WHEN salary IS NULL   THEN 'Not set'
       ELSE 'Entry' END AS band,
  COUNT(*) AS employees
FROM employees
GROUP BY band
ORDER BY employees DESC;

-- coalesce
SELECT emp_name, COALESCE(salary, 0) AS salary
FROM employees WHERE emp_id IN (9, 10);

-- left_join_where_trap
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'Returned';

-- left_join_on_fix
SELECT c.customer_name, COUNT(o.order_id) AS returned_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'Returned'
GROUP BY c.customer_name
ORDER BY returned_orders DESC, c.customer_name;

-- avg_null
SELECT AVG(salary) AS avg_ignoring_null,
       AVG(COALESCE(salary,0)) AS avg_null_as_zero
FROM employees;

-- consecutive_months
WITH m AS (
  SELECT DISTINCT customer_id,
         CAST(strftime('%Y', order_date) AS INT)*12 + CAST(strftime('%m', order_date) AS INT) AS month_no
  FROM orders WHERE status = 'Delivered'
)
SELECT DISTINCT a.customer_id
FROM m a
JOIN m b ON a.customer_id = b.customer_id AND b.month_no = a.month_no + 1
JOIN m c ON a.customer_id = c.customer_id AND c.month_no = a.month_no + 2;

-- self_join_count
SELECT m.emp_name AS manager, COUNT(e.emp_id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.emp_id
GROUP BY m.emp_name
ORDER BY direct_reports DESC;

-- recursive
WITH RECURSIVE chain AS (
  SELECT emp_id, emp_name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, e.manager_id, c.level + 1
  FROM employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT emp_name, level FROM chain ORDER BY level, emp_name;

-- last90
SELECT emp_name, hire_date FROM employees
WHERE hire_date >= DATE('2026-11-12', '-90 days');

-- pct_of_total
SELECT customer_id, SUM(amount) AS revenue,
  ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_total
FROM orders WHERE status = 'Delivered'
GROUP BY customer_id
ORDER BY revenue DESC;

-- union
SELECT city FROM customers
UNION
SELECT city FROM departments;

-- moving_avg
SELECT order_id, amount,
  ROUND(AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),0) AS moving_avg_3
FROM orders WHERE customer_id=101;

-- first_last
SELECT DISTINCT customer_id,
  FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order
FROM orders ORDER BY customer_id;

-- nested_agg_dept
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d LEFT JOIN employees e ON d.dept_id=e.dept_id
GROUP BY d.dept_name ORDER BY headcount DESC;

-- full_outer
SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL OR d.dept_id IS NULL;

-- div_int
SELECT 5600/12000 AS int_div, 5600*1.0/12000 AS dec_div