100 SQL Interview Queries Sheet

SELECT column1, column2, COUNT(*)
FROM your_table
GROUP BY column1, column2
HAVING COUNT(*) > 1;

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (
    SELECT MAX(salary) FROM Employee
);

SELECT e.*
FROM Employee e
LEFT JOIN Department d
  ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

SELECT *
FROM Employee
WHERE hire_date >= '2023-01-01' 
  AND hire_date <= '2023-12-31';

SELECT *
FROM Employee
WHERE salary BETWEEN 50000 AND 100000;

SELECT *
FROM Employee
WHERE WEEKDAY(hire_date) IN (5, 6);

\1>\1
\1>\1

SELECT p.product_id, p.product_name
FROM Products p
LEFT JOIN Sales s 
  ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

SELECT *
FROM Orders
WHERE total_amount > (
    SELECT AVG(total_amount) FROM Orders
);

SELECT customer_id, customer_name, registration_date
FROM Customers
WHERE status = 'Active'
  AND registration_date >= '2023-01-01'
  AND registration_date < '2024-01-01'
ORDER BY registration_date ASC
LIMIT 10;

SELECT product_id, product_name, price, stock_quantity
FROM Products
WHERE price > 100.00
  AND stock_quantity < 10;

SELECT employee_id, first_name, last_name
FROM Employee
WHERE first_name LIKE 'J%n';

SELECT order_id, order_date, status
FROM Orders
WHERE status IN ('Pending', 'Processing')
  AND order_date < DATE_SUB(NOW(), INTERVAL 7 DAY);

SELECT customer_id, customer_name
FROM Customers
WHERE email IS NULL OR email = '';

SELECT employee_id, first_name, manager_id
FROM Employee
WHERE manager_id IN (3, 7, 9);

SELECT DISTINCT city
FROM Customers
WHERE city IS NOT NULL
ORDER BY city ASC;

SELECT product_id, product_name
FROM Products
WHERE LOWER(product_name) LIKE '%pro%';

SELECT employee_id, first_name, salary
FROM Employee
WHERE salary NOT BETWEEN 40000 AND 80000;

SELECT order_id, order_date, total_amount
FROM Orders
WHERE order_date >= '2023-01-01' AND order_date < '2023-07-01';

SELECT employee_id, first_name, last_name, job_title
FROM Employee
WHERE manager_id IS NULL;

SELECT order_id, total_amount
FROM Orders
WHERE MOD(total_amount, 2) = 0;

\1>\1
\1>\1

SELECT customer_id, customer_name, phone
FROM Customers
WHERE phone LIKE '+1%';

SELECT product_id, product_name, CHAR_LENGTH(description) AS desc_len
FROM Products
WHERE CHAR_LENGTH(description) > 100;

\1>\1
\1>\1

SELECT order_id, order_date, total_amount
FROM Orders
WHERE order_date >= '2023-01-01' AND order_date < '2023-04-01';

SELECT contract_id, client_name, end_date
FROM Contracts
WHERE status = 'Active'
  AND end_date >= CURDATE()
  AND end_date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY);

\1>\1
\1>\1

SELECT employee_id, first_name, job_title, department_id
FROM Employee
WHERE department_id = 5
  AND job_title LIKE '%Manager%';

SELECT product_id,
       SUM(quantity * price) AS total_revenue
FROM Sales
GROUP BY product_id;

SELECT customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY customer_id;

SELECT customer_id,
       AVG(total_amount) AS avg_order_value
FROM Orders
GROUP BY customer_id;

SELECT customer_id,
       MAX(order_date) AS latest_order_date
FROM Orders
GROUP BY customer_id;

SELECT region,
       SUM(total_amount) AS total_revenue,
       COUNT(*) AS order_count
FROM Orders
GROUP BY region;

SELECT COUNT(*) AS customer_count
FROM (
    SELECT customer_id
    FROM Orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
) AS subquery;

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(total_amount) AS total_revenue,
       COUNT(order_id) AS order_count
FROM Orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

SELECT department_id,
       AVG(salary) AS avg_salary
FROM Employee
GROUP BY department_id
ORDER BY avg_salary DESC;

SELECT department_id,
       COUNT(*) AS emp_count,
       COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Employee) AS pct
FROM Employee
GROUP BY department_id;

SELECT department_id,
       MAX(salary) - MIN(salary) AS salary_diff
FROM Employee
GROUP BY department_id;

SELECT p.category, SUM(s.quantity) AS total_qty
FROM Products p
JOIN Sales s ON p.product_id = s.product_id
GROUP BY p.category;

SELECT DATE(login_time) AS login_date, COUNT(DISTINCT user_id) AS active_users
FROM UserLogs
GROUP BY DATE(login_time);

SELECT MAX(salary) AS max_sal, MIN(salary) AS min_sal, AVG(salary) AS avg_sal
FROM Employee;

SELECT region, SUM(total_amount) AS total_revenue
FROM Orders
GROUP BY region
HAVING SUM(total_amount) > 1000000.00;

SELECT 
    CASE 
        WHEN price < 50.00 THEN 'Budget'
        WHEN price BETWEEN 50.00 AND 100.00 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_tier,
    COUNT(*) AS product_count
FROM Products
GROUP BY 
    CASE 
        WHEN price < 50.00 THEN 'Budget'
        WHEN price BETWEEN 50.00 AND 100.00 THEN 'Mid-range'
        ELSE 'Premium'
    END;

SELECT shipping_method, AVG(DATEDIFF(shipped_date, order_date)) AS avg_processing_days
FROM Orders
WHERE shipped_date IS NOT NULL
GROUP BY shipping_method;

SELECT DAYNAME(order_date) AS day_of_week, 
       COUNT(order_id) AS order_count,
       SUM(total_amount) AS total_sales
FROM Orders
GROUP BY DAYNAME(order_date), DAYOFWEEK(order_date)
ORDER BY DAYOFWEEK(order_date);

SELECT department_id, COUNT(*) AS employee_count
FROM Employee
GROUP BY department_id
HAVING COUNT(*) > 10;

SELECT department_id, AVG(DATEDIFF(CURRENT_DATE, birth_date) / 365.25) AS avg_age
FROM Employee
GROUP BY department_id;

SELECT c.city, COUNT(DISTINCT s.product_id) AS unique_products_sold
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Sales s ON o.order_id = s.order_id
GROUP BY c.city;

SELECT customer_id, 
       MIN(total_amount) AS min_order,
       MAX(total_amount) AS max_order
FROM Orders
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
GROUP BY customer_id;

SELECT sales_rep_id, SUM(deal_amount * commission_rate) AS total_commission
FROM Deals
GROUP BY sales_rep_id;

SELECT QUARTER(hire_date) AS qtr, COUNT(*) AS hires_count
FROM Employee
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01'
GROUP BY QUARTER(hire_date)
ORDER BY qtr;

SELECT product_id, AVG(rating) AS avg_rating
FROM Reviews
GROUP BY product_id
HAVING AVG(rating) > 4.0
ORDER BY avg_rating DESC;

SELECT carrier_id, SUM(weight) AS total_weight
FROM Shipments
WHERE status = 'Shipped'
GROUP BY carrier_id
HAVING SUM(weight) > 500.00;

SELECT *
FROM Employee
ORDER BY salary DESC
LIMIT 3;

\1>\1
\1>\1

SELECT DISTINCT o.customer_id
FROM Orders o
LEFT JOIN Returns r ON o.customer_id = r.customer_id
WHERE r.customer_id IS NULL;

WITH TotalRevenue AS (
    SELECT SUM(quantity * price) AS total FROM Sales
)
SELECT s.product_id,
       SUM(s.quantity * s.price) AS revenue,
       SUM(s.quantity * s.price) * 100.0 / t.total AS revenue_pct
FROM Sales s
CROSS JOIN TotalRevenue t
GROUP BY s.product_id, t.total;

SELECT customer_id
FROM Orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY customer_id
HAVING MAX(order_date) < DATE_SUB(NOW(), INTERVAL 6 MONTH);

WITH customer_orders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM Orders
    GROUP BY customer_id
)
SELECT customer_id, order_count
FROM customer_orders
WHERE order_count > (
    SELECT AVG(order_count) FROM customer_orders
);

WITH first_orders AS (
    SELECT customer_id, MIN(order_date) AS first_order_date 
    FROM Orders
    GROUP BY customer_id
)
SELECT SUM(o.total_amount) AS new_revenue
FROM Orders o 
JOIN first_orders f 
  ON o.customer_id = f.customer_id
WHERE o.order_date = f.first_order_date;

WITH cte AS (
    SELECT customer_id, SUM(total_amount) AS total_revenue
    FROM Orders
    GROUP BY customer_id
)
SELECT customer_id, total_revenue
FROM cte
WHERE total_revenue < (
    SELECT PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY total_revenue) FROM cte
);

SELECT s1.product_id AS p1, s2.product_id AS p2, COUNT(*) AS times_bought_together
FROM Sales s1
JOIN Sales s2 ON s1.order_id = s2.order_id
  AND s1.product_id < s2.product_id
GROUP BY s1.product_id, s2.product_id
ORDER BY times_bought_together DESC
LIMIT 10;

SELECT e.first_name AS employee, m.first_name AS manager
FROM Employee e
LEFT JOIN Employee m ON e.manager_id = m.employee_id;

SELECT s.customer_id
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY s.customer_id
HAVING COUNT(DISTINCT p.category) = (SELECT COUNT(DISTINCT category) FROM Products);

SELECT s.product_id
FROM Sales s
JOIN Orders o ON s.order_id = o.order_id
GROUP BY s.product_id
HAVING COUNT(DISTINCT o.region) = (SELECT COUNT(DISTINCT region) FROM Orders);

SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM Employee e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM Employee 
    WHERE department_id = e.department_id
);

SELECT o.order_id, o.order_date, c.customer_name, c.shipping_address
FROM Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id;

SELECT c.customer_id, c.customer_name, e.email_address AS secondary_email
FROM Customers c
INNER JOIN CustomerEmails e 
  ON c.customer_id = e.customer_id
WHERE e.email_type = 'Secondary';

SELECT d.department_id, d.department_name
FROM Department d
LEFT JOIN Employee e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

SELECT project_id, project_name, cost
FROM Projects
WHERE cost > (
    SELECT AVG(cost) FROM Projects
);

SELECT e.employee_id, e.first_name, p.project_name
FROM Employee e
LEFT JOIN ProjectAssignments pa ON e.employee_id = pa.employee_id
LEFT JOIN Projects p ON pa.project_id = p.project_id;

SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 1;

\1>\1
\1>\1

SELECT i.item_id, i.item_name
FROM Inventory i
WHERE NOT EXISTS (
    SELECT 1 
    FROM OrderItems o 
    WHERE o.item_id = i.item_id
);

SELECT e1.employee_id AS emp1, e2.employee_id AS emp2, e1.hire_date
FROM Employee e1
JOIN Employee e2 ON e1.hire_date = e2.hire_date
  AND e1.employee_id < e2.employee_id
ORDER BY e1.hire_date;

SELECT SUM(o.total_amount) AS tech_sales
FROM Orders o
WHERE o.customer_id IN (
    SELECT customer_id 
    FROM Customers 
    WHERE industry = 'Technology'
);

SELECT c.customer_id, c.customer_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE DATE(o.order_date) = DATE(c.registration_date);

SELECT m.employee_id, m.first_name AS manager_name, COUNT(*) AS reportee_count
FROM Employee e
JOIN Employee m ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.first_name
HAVING COUNT(*) > 5;

SELECT DISTINCT p.product_id, p.product_name
FROM Products p
JOIN PriceHistory h ON p.product_id = h.product_id
WHERE h.change_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS ord_count
    FROM Orders
    GROUP BY customer_id
)
SELECT c.customer_id, c.customer_name
FROM Customers c
JOIN order_counts o ON c.customer_id = o.customer_id
WHERE o.ord_count >= 3
  AND EXISTS (
      SELECT 1 FROM Reviews r WHERE r.customer_id = c.customer_id
  );

SELECT employee_id, department_id, salary,
       RANK() OVER (
           PARTITION BY department_id 
           ORDER BY salary DESC
       ) AS salary_rk
FROM Employee;

SELECT customer_id
FROM Orders
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
GROUP BY customer_id
HAVING COUNT(DISTINCT DATE_FORMAT(order_date, '%Y-%m')) = 12;

SELECT order_date, total_amount,
       AVG(total_amount) OVER (
           ORDER BY order_date 
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg
FROM Orders;

SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order
FROM Orders
GROUP BY customer_id;

WITH cte AS (
    SELECT id, order_date,
           LAG(order_date) OVER (PARTITION BY id ORDER BY order_date) AS prev_order_date
    FROM Orders
)
SELECT DISTINCT id, order_date, prev_order_date
FROM cte
WHERE prev_order_date IS NOT NULL 
  AND DATEDIFF(DAY, prev_order_date, order_date) = 1;

SELECT order_date, SUM(total_amount) AS daily_sales,
       SUM(SUM(total_amount)) OVER (ORDER BY order_date) AS cumulative_revenue
FROM Orders
GROUP BY order_date;

WITH sales_cte AS (
    SELECT product_id, SUM(quantity * price) AS revenue
    FROM Sales GROUP BY product_id
),
total_revenue AS (
    SELECT SUM(revenue) AS total FROM sales_cte
),
running_sales AS (
    SELECT s.product_id, s.revenue,
           SUM(s.revenue) OVER (ORDER BY s.revenue DESC) AS running_total,
           t.total
    FROM sales_cte s
    CROSS JOIN total_revenue t
)
SELECT product_id, revenue, running_total
FROM running_sales
WHERE running_total <= total * 0.8;

WITH cte AS (
    SELECT customer_id, order_date,
           LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_date
    FROM Orders
)
SELECT customer_id,
       AVG(DATEDIFF(order_date, prev_date)) AS avg_gap_days 
FROM cte
WHERE prev_date IS NOT NULL
GROUP BY customer_id;

WITH ranked_orders AS (
    SELECT customer_id, order_id, total_amount, 
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn 
    FROM Orders
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE rn = 1;

WITH yearly_revenue AS (
    SELECT DATE_FORMAT(order_date, '%Y') AS year,
           SUM(total_amount) AS revenue
    FROM Orders
    GROUP BY DATE_FORMAT(order_date, '%Y')
)
SELECT year, revenue,
       LAG(revenue) OVER (ORDER BY year) AS prev_year_revenue,
       revenue - LAG(revenue) OVER (ORDER BY year) AS yoy_growth_amount,
       (revenue - LAG(revenue) OVER (ORDER BY year)) * 100.0 / LAG(revenue) OVER (ORDER BY year) AS yoy_growth_pct
FROM yearly_revenue;

WITH ranked_orders AS (
    SELECT customer_id, order_id, total_amount,
           NTILE(10) OVER (PARTITION BY customer_id ORDER BY total_amount) AS decile
    FROM Orders
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE decile = 10;

WITH cte AS (
    SELECT customer_id, order_date,
           LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date 
    FROM Orders
)
SELECT customer_id, 
       MAX(DATEDIFF(order_date, prev_order_date)) AS max_gap_days
FROM cte
WHERE prev_order_date IS NOT NULL
GROUP BY customer_id;

SELECT signup_date, COUNT(*) AS daily_signups,
       SUM(COUNT(*)) OVER (ORDER BY signup_date) AS cumulative_signups
FROM Users
GROUP BY signup_date;

SELECT employee_id, first_name, department_id, salary,
       MAX(salary) OVER (PARTITION BY department_id) AS max_dep_salary,
       MAX(salary) OVER (PARTITION BY department_id) - salary AS salary_gap
FROM Employee;

WITH cte AS (
    SELECT customer_id, order_date,
           LEAD(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_date,
           LEAD(order_date, 2) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_next_date
    FROM (
        SELECT DISTINCT customer_id, DATE(order_date) AS order_date FROM Orders
    ) d
)
SELECT DISTINCT customer_id
FROM cte
WHERE DATEDIFF(next_date, order_date) = 1
  AND DATEDIFF(next_next_date, next_date) = 1;

WITH product_sales AS (
    SELECT p.category, p.product_id, p.product_name, SUM(s.quantity) AS total_qty
    FROM Products p
    JOIN Sales s ON p.product_id = s.product_id
    GROUP BY p.category, p.product_id, p.product_name
)
SELECT category, product_id, product_name, total_qty,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY total_qty DESC) AS product_rank
FROM product_sales;

SELECT order_id, order_date, shipped_date,
       DATEDIFF(shipped_date, order_date) AS immediate_lead_time,
       DATEDIFF(
           LEAD(order_date) OVER (ORDER BY order_date),
           order_date
       ) AS time_until_next_order
FROM Orders;

WITH monthly_active AS (
    SELECT DATE_FORMAT(login_time, '%Y-%m') AS month,
           COUNT(DISTINCT user_id) AS active_users
    FROM UserLogs
    GROUP BY DATE_FORMAT(login_time, '%Y-%m')
)
SELECT month, active_users,
       LAG(active_users) OVER (ORDER BY month) AS prev_month_users,
       (active_users - LAG(active_users) OVER (ORDER BY month)) * 100.0 / LAG(active_users) OVER (ORDER BY month) AS mom_pct_change
FROM monthly_active;

WITH ranked_orders AS (
    SELECT customer_id, order_id, order_date, total_amount,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS rn
    FROM Orders
)
SELECT customer_id, order_id, order_date, total_amount
FROM ranked_orders
WHERE rn = 2;

WITH customer_spend AS (
    SELECT c.country, c.customer_id, c.customer_name, SUM(o.total_amount) AS total_spend
    FROM Customers c
    JOIN Orders o ON c.customer_id = o.customer_id
    GROUP BY c.country, c.customer_id, c.customer_name
)
SELECT country, customer_id, customer_name, total_spend,
       SUM(total_spend) OVER (
           PARTITION BY country 
           ORDER BY total_spend DESC 
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_ltv_by_country
FROM customer_spend;

WITH daily_counts AS (
    SELECT DATE(order_date) AS o_date, COUNT(*) AS order_count
    FROM Orders
    GROUP BY DATE(order_date)
)
SELECT o_date, order_count,
       AVG(order_count) OVER (
           ORDER BY o_date 
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_avg_7d
FROM daily_counts;

WITH product_revenue AS (
    SELECT product_id, SUM(quantity * price) AS revenue
    FROM Sales
    GROUP BY product_id
),
ranked_products AS (
    SELECT product_id, revenue,
           PERCENT_RANK() OVER (ORDER BY revenue DESC) AS pct_rank
    FROM product_revenue
)
SELECT product_id, revenue, pct_rank
FROM ranked_products
WHERE pct_rank <= 0.05;

SELECT category, product_id, product_name, price,
       LAG(price) OVER (PARTITION BY category ORDER BY price ASC) AS prev_price,
       (price - LAG(price) OVER (PARTITION BY category ORDER BY price ASC)) * 100.0 / 
       LAG(price) OVER (PARTITION BY category ORDER BY price ASC) AS price_diff_pct
FROM Products;

WITH ranked_employees AS (
    SELECT employee_id, first_name, salary, department_id,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS drk
    FROM Employee
)
SELECT employee_id, first_name, salary, department_id, drk
FROM ranked_employees
WHERE drk <= 3;

WITH session_lag AS (
    SELECT session_id, user_id, action_time, 
           LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time) AS prev_action_time
    FROM UserActions
)
SELECT session_id, user_id, action_time, prev_action_time,
       DATEDIFF(MINUTE, prev_action_time, action_time) AS minutes_since_prev
FROM session_lag
WHERE prev_action_time IS NOT NULL
  AND DATEDIFF(MINUTE, prev_action_time, action_time) <= 10;