Preguntas de entrevista de SQL con ejercicios y respuestas (45+)

-- WHERE filtra filas individuales antes de agrupar
SELECT department, COUNT(*) AS total
FROM employees
WHERE salary > 50000
GROUP BY department;

-- HAVING filtra el resultado del GROUP BY
SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- Crear un índice simple
CREATE INDEX idx_employees_email ON employees(email);

-- Índice compuesto (el orden importa)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Datos de ejemplo
-- tabla users: id, name
-- tabla orders: id, user_id, amount

-- INNER JOIN: solo los usuarios que tienen pedidos
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: todos los usuarios, tengan pedido o no
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Los usuarios sin pedidos aparecen con NULL en o.amount

-- RIGHT JOIN: todos los pedidos, aunque el usuario no exista
SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN: todos los registros de ambas tablas
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- INCORRECTO: esto nunca devuelve filas
SELECT * FROM users WHERE phone = NULL;

-- CORRECTO
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- COALESCE: devuelve el primer valor no-NULL
SELECT name, COALESCE(phone, 'sin teléfono') AS phone_display
FROM users;

-- NULLIF: devuelve NULL si los dos valores son iguales (útil para evitar división por cero)
SELECT revenue / NULLIF(visits, 0) AS revenue_per_visit
FROM campaigns;

-- UNION: elimina duplicados (más lento, hace un DISTINCT implícito)
SELECT email FROM users_argentina
UNION
SELECT email FROM users_colombia;

-- UNION ALL: mantiene duplicados (más rápido)
SELECT email FROM users_argentina
UNION ALL
SELECT email FROM users_colombia;

CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,           -- PRIMARY KEY: unicidad + NOT NULL
  email       VARCHAR(255) UNIQUE NOT NULL,  -- UNIQUE: sin duplicados
  department_id INT REFERENCES departments(id), -- FOREIGN KEY: integridad referencial
  salary      NUMERIC CHECK (salary > 0),   -- CHECK: validación de dominio
  status      VARCHAR(20) DEFAULT 'active'  -- DEFAULT: valor por defecto
);

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- Si algo falla entre las dos líneas, hacemos ROLLBACK

-- DELETE: borra filas que cumplan la condición (se puede hacer ROLLBACK)
DELETE FROM logs WHERE created_at < '2025-01-01';

-- TRUNCATE: borra TODAS las filas, más rápido que DELETE, no hace log por fila
-- En PostgreSQL, se puede hacer ROLLBACK. En MySQL, no.
TRUNCATE TABLE logs;

-- DROP: elimina la tabla completamente (estructura + datos)
DROP TABLE logs;

-- Para cada empleado, encontrar si su salario es mayor al promedio de su departamento
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id  -- referencia a la consulta exterior
);

-- Encontrar emails duplicados
SELECT email, COUNT(*) AS cantidad
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Ver los registros completos de los duplicados
SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
)
ORDER BY email;

-- Una vista es una consulta guardada, no almacena datos
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- Se usa igual que una tabla
SELECT * FROM active_users;

-- PostgreSQL
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS total
FROM orders
GROUP BY 1;

-- Refrescar manualmente
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refrescar sin bloquear lecturas (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- Opción 1: con subconsulta
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Opción 2: con OFFSET (más limpio)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Opción 3: con window function (la más robusta para el N-ésimo)
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue
FROM (
  SELECT
    DATE(created_at) AS order_date,
    SUM(amount) AS daily_revenue
  FROM orders
  GROUP BY DATE(created_at)
) daily_totals
ORDER BY order_date;

-- Opción 1: LEFT JOIN con filtro NULL
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Opción 2: NOT EXISTS (generalmente más eficiente en tablas grandes)
SELECT id, name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Opción 3: NOT IN (cuidado con NULLs)
SELECT id, name
FROM users
WHERE id NOT IN (
  SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
);

SELECT
  category,
  COUNT(*) AS quantity,
  ROUND(
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (),
    2
  ) AS percentage
FROM products
GROUP BY category
ORDER BY percentage DESC;

SELECT user_id
FROM orders
WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '1 year')
  AND created_at < DATE_TRUNC('year', CURRENT_DATE)
GROUP BY user_id
HAVING COUNT(DISTINCT DATE_TRUNC('month', created_at)) = 12;

-- PostgreSQL tiene PERCENTILE_CONT
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS mediana
FROM employees;

-- Para MySQL o SQLite (sin esa función):
SELECT AVG(salary) AS mediana
FROM (
  SELECT salary
  FROM (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS total
    FROM employees
  ) ranked
  WHERE rn IN (FLOOR((total + 1) / 2.0), CEIL((total + 1) / 2.0))
) median_rows;

WITH ranked_products AS (
  SELECT
    p.category,
    p.name,
    SUM(oi.quantity) AS total_sold,
    RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC) AS rnk
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  GROUP BY p.category, p.name
)
SELECT category, name, total_sold
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

-- Estructura de empleados con managers
-- tabla: employees (id, name, manager_id)

SELECT
  e.name AS empleado,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

WITH events_with_prev AS (
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time
  FROM user_events
),
session_starts AS (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN prev_event_time IS NULL
        OR event_time - prev_event_time > INTERVAL '30 minutes'
      THEN 1
      ELSE 0
    END AS is_new_session
  FROM events_with_prev
),
sessions AS (
  SELECT
    user_id,
    event_time,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM session_starts
)
SELECT user_id, session_id, MIN(event_time) AS session_start, MAX(event_time) AS session_end
FROM sessions
GROUP BY user_id, session_id;

-- Convertir filas de categorías en columnas
-- Tabla: sales (month, category, amount)

SELECT
  month,
  SUM(CASE WHEN category = 'electronics' THEN amount ELSE 0 END) AS electronics,
  SUM(CASE WHEN category = 'clothing'    THEN amount ELSE 0 END) AS clothing,
  SUM(CASE WHEN category = 'food'        THEN amount ELSE 0 END) AS food
FROM sales
GROUP BY month
ORDER BY month;

WITH monthly_users AS (
  SELECT DISTINCT
    DATE_TRUNC('month', event_date) AS month,
    user_id
  FROM user_activity
),
user_months AS (
  SELECT
    user_id,
    month,
    LAG(month) OVER (PARTITION BY user_id ORDER BY month) AS prev_month
  FROM monthly_users
)
SELECT
  month,
  COUNT(DISTINCT CASE WHEN prev_month IS NULL OR
    prev_month < month - INTERVAL '1 month' THEN user_id END) AS new_or_returned,
  COUNT(DISTINCT user_id) AS active_users
FROM user_months
GROUP BY month
ORDER BY month;

-- En PostgreSQL, usando ctid (identificador interno de fila)
DELETE FROM users
WHERE ctid NOT IN (
  SELECT MIN(ctid)
  FROM users
  GROUP BY email  -- o los campos que definen el duplicado
);

-- Alternativa con CTE (más legible)
WITH duplicates AS (
  SELECT id,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (
  SELECT id FROM duplicates WHERE rn > 1
);

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM users
  GROUP BY user_id
),
activity AS (
  SELECT
    e.user_id,
    c.cohort_month,
    DATE_TRUNC('month', e.event_date) AS activity_month,
    EXTRACT(EPOCH FROM (DATE_TRUNC('month', e.event_date) - c.cohort_month)) / (30 * 86400) AS months_since_signup
  FROM events e
  JOIN cohorts c ON e.user_id = c.user_id
)
SELECT
  cohort_month,
  months_since_signup::INT AS month_number,
  COUNT(DISTINCT user_id) AS retained_users,
  FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
    PARTITION BY cohort_month ORDER BY months_since_signup
  ) AS cohort_size,
  ROUND(
    COUNT(DISTINCT user_id) * 100.0 /
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
      PARTITION BY cohort_month ORDER BY months_since_signup
    ), 1
  ) AS retention_rate
FROM activity
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, month_number;

SELECT
  name,
  department,
  salary,
  -- OVER () sin PARTITION: toda la tabla es la ventana
  AVG(salary) OVER () AS company_avg,

  -- PARTITION BY: ventana por departamento
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,

  -- ORDER BY dentro de la ventana: acumulado
  SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cumulative_payroll,

  -- Funciones de ranking
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM employees;

-- Sin CTE: difícil de leer
SELECT *
FROM (
  SELECT user_id, COUNT(*) AS orders, SUM(amount) AS revenue
  FROM orders
  GROUP BY user_id
) user_stats
WHERE revenue > 1000;

-- Con CTE: mucho más legible
WITH user_stats AS (
  SELECT user_id, COUNT(*) AS orders, SUM(amount) AS revenue
  FROM orders
  GROUP BY user_id
)
SELECT *
FROM user_stats
WHERE revenue > 1000;

WITH RECURSIVE org_chart AS (
  -- Caso base: el CEO (sin manager)
  SELECT id, name, manager_id, 0 AS level, name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Caso recursivo: los reportes de cada manager
  SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' > ' || e.name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, name, path
FROM org_chart
ORDER BY path;

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Cambiar el nivel de aislamiento en PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... tus queries
COMMIT;

-- Índice compuesto
CREATE INDEX idx_orders_user_date_status ON orders(user_id, created_at, status);

-- USA el índice (empieza desde la izquierda)
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2025-01-01';
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2025-01-01' AND status = 'paid';

-- NO usa el índice (salta el primer campo)
SELECT * FROM orders WHERE created_at > '2025-01-01';
SELECT * FROM orders WHERE status = 'paid';

-- Encontrar números faltantes en una secuencia
WITH consecutive AS (
  SELECT
    id,
    LEAD(id) OVER (ORDER BY id) AS next_id
  FROM invoices
)
SELECT
  id + 1 AS gap_start,
  next_id - 1 AS gap_end
FROM consecutive
WHERE next_id > id + 1;

-- MALO: 1 query para usuarios, N queries para los pedidos de cada uno
-- SELECT * FROM users;
-- Para cada usuario: SELECT * FROM orders WHERE user_id = ?

-- BUENO: un solo JOIN
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM (
  SELECT DATE(created_at) AS order_date, SUM(amount) AS daily_revenue
  FROM orders
  GROUP BY DATE(created_at)
) daily
ORDER BY order_date;

WITH recent AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
),
previous AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_date >= CURRENT_DATE - INTERVAL '60 days'
    AND event_date < CURRENT_DATE - INTERVAL '30 days'
)
SELECT r.user_id
FROM recent r
LEFT JOIN previous p ON r.user_id = p.user_id
WHERE p.user_id IS NULL;

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM results;

/*
name    | score | row_num | rank | dense_rank
--------|-------|---------|------|------------
Ana     | 95    | 1       | 1    | 1
Carlos  | 95    | 2       | 1    | 1
Lucia   | 87    | 3       | 3    | 2
Pedro   | 80    | 4       | 4    | 3
*/

SELECT
  department,
  name,
  salary,
  -- Promedio POR departamento (no de toda la empresa)
  ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg,
  -- Posición dentro de su departamento
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  -- Posición en la empresa entera
  RANK() OVER (ORDER BY salary DESC) AS company_rank
FROM employees;

WITH user_revenue AS (
  SELECT
    u.id AS user_id,
    u.acquisition_source,
    SUM(o.amount) AS total_revenue
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.acquisition_source
)
SELECT
  acquisition_source,
  COUNT(*) AS total_users,
  ROUND(AVG(total_revenue), 2) AS avg_ltv,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue), 2) AS median_ltv,
  SUM(total_revenue) AS total_revenue
FROM user_revenue
GROUP BY acquisition_source
ORDER BY avg_ltv DESC;

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  price       NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock       INT NOT NULL DEFAULT 0,
  category_id INT REFERENCES categories(id)
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT NOT NULL REFERENCES users(id),
  status     VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  total      NUMERIC(10,2)
);

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  order_id   INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2) NOT NULL  -- precio al momento de la compra
);

-- Tabla de historial de precios
CREATE TABLE product_prices (
  id         SERIAL PRIMARY KEY,
  product_id INT NOT NULL REFERENCES products(id),
  price      NUMERIC(10,2) NOT NULL,
  valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  valid_to   TIMESTAMPTZ  -- NULL significa "precio actual"
);

-- Consultar el precio en un momento dado
SELECT price
FROM product_prices
WHERE product_id = 42
  AND valid_from <= '2025-06-15 10:00:00'
  AND (valid_to IS NULL OR valid_to > '2025-06-15 10:00:00');

-- Actualizar el precio (dejar rastro)
UPDATE product_prices
SET valid_to = NOW()
WHERE product_id = 42 AND valid_to IS NULL;

INSERT INTO product_prices (product_id, price, valid_from)
VALUES (42, 29.99, NOW());

-- Transacción A          | Transacción B
-- UPDATE accounts SET... |
-- WHERE id = 1;          | UPDATE accounts SET...
--                        | WHERE id = 2;
-- UPDATE accounts SET... | -- espera a A
-- WHERE id = 2; -- espera a B  |

-- Adquirir el lock en orden ascendente de id
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Agregar columna de soft delete
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Eliminar (marcar como borrado)
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- Consultar solo activos
SELECT * FROM users WHERE deleted_at IS NULL;

-- Índice parcial para que las queries de activos sean rápidas
CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;

-- Vista para simplificar las queries
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- El tercer pedido de cada cliente (por fecha)
WITH ranked_orders AS (
  SELECT
    user_id,
    id AS order_id,
    amount,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_number
  FROM orders
)
SELECT user_id, order_id, amount, created_at
FROM ranked_orders
WHERE order_number = 3;

-- MALO: la función impide usar el índice
SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';

-- BUENO: rango que puede usar el índice
SELECT * FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at < '2025-01-16';

-- MALO: cálculo en columna indexada
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- BUENO: índice funcional
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Tabla de hechos (fact table): las métricas
CREATE TABLE fact_sales (
  sale_id      BIGINT PRIMARY KEY,
  date_id      INT REFERENCES dim_date(date_id),
  product_id   INT REFERENCES dim_product(product_id),
  customer_id  INT REFERENCES dim_customer(customer_id),
  store_id     INT REFERENCES dim_store(store_id),
  quantity     INT,
  revenue      NUMERIC(12,2)
);

-- Dimensiones: el contexto
CREATE TABLE dim_date (
  date_id    INT PRIMARY KEY,
  date       DATE,
  year       INT,
  quarter    INT,
  month      INT,
  week       INT,
  day_name   VARCHAR(10)
);

CREATE TABLE dim_product (
  product_id   INT PRIMARY KEY,
  name         VARCHAR(255),
  category     VARCHAR(100),
  brand        VARCHAR(100)
);

SELECT region, category, SUM(revenue)
FROM sales
GROUP BY ROLLUP(region, category);
-- Devuelve: total por (region, category), total por region, y gran total

-- El pedido más reciente de cada cliente
SELECT DISTINCT ON (user_id) user_id, id, created_at
FROM orders
ORDER BY user_id, created_at DESC;