Prompt y contexto aplicable
Usando PostgreSQL, retorna los tres niveles de ingresos de producto distintos más altos en cada categoría para el Q2 2026, incluyendo cada producto empatado en el tercer nivel. Establece el contrato de datos, escribe el SQL, y explica por qué ROW_NUMBER, RANK y DENSE_RANK producen resultados distintos.
El esquema es:
orders( order_id bigint primary key, ordered_at timestamptz, status text )
order_items( order_id bigint, category_id bigint, product_id bigint, quantity integer, unit_price numeric(12, 2) )
Usa este contrato acordado: cuenta solo las órdenes COMPLETED; define el trimestre como el intervalo medio-abierto UTC [2026-04-01, 2026-07-01); el ingreso de producto es la suma de quantity * unit_price sobre los artículos de línea elegibles; los reembolsos están fuera del modelo provisto; y "los tres principales" significa los tres niveles de ingresos distintos más altos por categoría, por lo que cada producto empatado en el tercer nivel debe ser retornado. Los productos sin ventas elegibles están ausentes, y una categoría con menos de tres niveles retorna todos los niveles que tenga.
Esta pregunta de dificultad media aplica a roles de analista de datos, ingeniero de datos y backend que requieren SQL analítico. Los materiales de entrevista de SQL de 2026 aún presentan top-N-por-grupo, funciones de ventana y el comportamiento en empates como ejercicios explícitos. No existe una atribución verificable a ninguna empresa, por lo que se trata como una pregunta representativa de entrevista de SQL.
Qué evalúa el entrevistador
La primera señal es si defines el grano de salida. Una fila de order_items es un artículo de línea, pero el objetivo es una fila por par categoría-producto. Rankear los artículos de línea sin procesar le da a un producto varias posiciones. Un SQL sintácticamente válido puede responder aun así la pregunta de negocio incorrecta.
La segunda señal es si traduces "los tres principales" a una semántica de empates explícita. ROW_NUMBER, RANK y DENSE_RANK pueden ordenar filas dentro de una categoría, pero responden preguntas distintas. Un candidato sólido primero pregunta si el resultado necesita exactamente tres filas, rangos de competencia o tres niveles de ingresos distintos.
La tercera señal es el conocimiento del orden de evaluación lógica de SQL. Las funciones de ventana se ejecutan después de WHERE, GROUP BY, HAVING y la agregación ordinaria. Agrega el ingreso de producto primero, rankéalo en el siguiente nivel y filtra el rango en una consulta exterior. PostgreSQL no puede usar el alias de ventana en la cláusula WHERE del mismo nivel de consulta.
La señal final es la verificabilidad y la mantenibilidad. Una respuesta sólida define el límite temporal, el estatus elegible, el tipo de dinero, el orden de presentación determinístico y el fixture de prueba. A escala, primero reduce las filas que ingresan a la etapa de ranking en lugar de ofrecer "agregar un índice" como remedio sin sustento.
Preguntas para aclarar antes de responder
- ¿"Los tres principales" significa tres filas, tres posiciones de competencia o tres valores distintos? Usa
ROW_NUMBERpara tres filas,RANKpara ranking de competencia yDENSE_RANKpara este requisito: tres niveles de ingresos distintos con empates preservados. - ¿Qué evento y zona horaria asignan ingresos a un período? Los tiempos de orden, pago, completitud y reembolso producen reportes distintos. Este prompt usa
orders.ordered_aten UTC con intervalos medio-abiertos no superpuestos. - ¿Qué estatus son elegibles? Incluir órdenes canceladas, de prueba o fallidas infla los ingresos. Este contrato cuenta solo
COMPLETED. - ¿Dónde están los reembolsos, descuentos, impuestos y monedas? El modelo provisto contiene solo cantidad y precio unitario de transacción. Si se agrega una tabla de reembolsos o múltiples monedas, calcula el ingreso neto o convierte a una sola moneda antes de rankear.
- ¿Puede un producto pertenecer a múltiples categorías? Esta consulta trata
(category_id, product_id)en el artículo de línea como la clave de hecho. Unir la dimensión de producto actual podría reescribir la atribución histórica de categoría; usa una instantánea en el momento de la orden o una dimensión con fechas efectivas cuando ese historial importe. - ¿Deben aparecer los productos sin ventas? No aparecen aquí. Si se requiere, haz un left join del agregado con un conjunto completo de categoría-producto y define si cero participa en los tres niveles principales.
- ¿El orden de salida debe ser determinístico? Rankea solo por ingreso; agregar
product_ida la ventana de ranking destruye los empates. Estabiliza la presentación por separado conORDER BY category_id, revenue_rank, product_id.
Marco de respuesta en 30 segundos
"Agregaría las órdenes completadas de UTC Q2 a una fila por categoría y producto, luego usaría DENSE_RANK por categoría e ingreso descendente porque el requisito es tres niveles distintos con empates. Un CTE calcula el rango, la consulta exterior filtra revenue_rank <= 3, y el ordenamiento final solo estabiliza la presentación. Probaría empates, límites del trimestre, órdenes canceladas y categorías con menos de tres niveles."
Una respuesta completa también debe explicar por qué la agregación precede al ranking, cómo difieren las tres funciones de ranking y cómo validar tanto el contrato de reporte como el plan de ejecución a escala.
Respuesta detallada paso a paso
Paso 1: Expresar la pregunta de negocio como grano e invariantes
La clave del resultado no es order_id; es (category_id, product_id). Cada clave debe aparecer una vez en la etapa de agregación, y revenue debe ser igual a la suma de todos los importes de artículos de línea elegibles para esa clave. El ranking agrega un nivel local de categoría sin cambiar el grano.
Establece tres invariantes antes de escribir la sintaxis: cada artículo de línea elegible contribuye exactamente una vez; el mismo producto en distintas órdenes se combina; y el revenue_rank de una categoría depende solo de los ingresos dentro de esa categoría. Estos invariantes exponen rápidamente los joins duplicados, el ranking de artículos de línea y el ranking global.
Paso 2: Filtrar los hechos antes de agregar al grano de producto
Usa >= para el inicio y < para el inicio del siguiente trimestre. A diferencia de BETWEEN o 2026-06-30 23:59:59, un intervalo medio-abierto no omite mayor precisión de timestamp y se compone limpiamente con el siguiente trimestre. El uso explícito de +00 en los literales timestamptz mantiene el contrato independiente de la zona horaria de la sesión de base de datos.
Después de hacer el join, filtra por estatus y tiempo, luego agrupa por category_id, product_id. En este modelo de PostgreSQL, quantity * unit_price sigue siendo una expresión numérica exacta y SUM preserva la aritmética monetaria exacta. No lo conviertas a punto flotante por conveniencia. Un reporte de ingresos en producción también necesita semántica de moneda y reembolso, pero las columnas provistas no pueden generarlas.
Paso 3: Elegir la función de ranking a partir del contrato de empates
Supón que una categoría tiene ingresos de producto 100, 100, 90, 80:
ROW_NUMBERproduce1, 2, 3, 4. Sin una clave adicional, las dos filas con 100 tienen un orden relativo no especificado. Este ejemplo retorna ambas filas con 100 y una fila con 90, pero un empate que cruce el límite de la tercera fila sería cortado arbitrariamente. Garantiza tres filas, no empates completos.RANKproduce1, 1, 3, 4. Filtrar en 3 retorna los ingresos 100 y 90, solo dos niveles distintos.DENSE_RANKproduce1, 1, 2, 3. Filtrar en 3 retorna los cuatro productos y exactamente tres niveles distintos.
Por lo tanto, este prompt necesita DENSE_RANK. Su ventana ORDER BY debe contener solo revenue DESC. Agregar product_id significa que las filas con ingresos iguales ya no son pares, reemplazando silenciosamente "preservar empates" con un orden fabricado.
Paso 4: Separar la agregación, el ranking y el filtrado con dos CTEs
La consulta completa de PostgreSQL es:
WITH product_revenue AS ( SELECT oi.category_id, oi.product_id, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders AS o JOIN order_items AS oi ON oi.orderid = o.orderid WHERE o.status = 'COMPLETED' AND o.ordered_at >= TIMESTAMPTZ '2026-04-01 00:00:00+00' AND o.ordered_at < TIMESTAMPTZ '2026-07-01 00:00:00+00' GROUP BY oi.categoryid, oi.productid ), ranked AS ( SELECT category_id, product_id, revenue, DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY revenue DESC ) AS revenue_rank FROM product_revenue ) SELECT categoryid, productid, revenue, revenue_rank FROM ranked WHERE revenue_rank <= 3 ORDER BY categoryid, revenuerank, product_id;
La primera etapa reduce muchos artículos de orden a hechos de producto. La segunda ordena solo el conjunto de productos dentro de cada categoría. La consulta exterior puede entonces ver y filtrar el resultado de la ventana. Las etapas reflejan el contrato de métrica, el contrato de ranking y el contrato de salida, por lo que su grano y cantidad de filas pueden inspeccionarse de forma independiente.
Paso 5: Demostrar la correctitud en lugar de solo mostrar sintaxis
Para cualquier categoría, la primera etapa crea un total por producto. PARTITION BY category_id excluye cada otra categoría de la comparación, y ORDER BY revenue DESC define los totales iguales como un grupo par. DENSE_RANK numera los grupos pares consecutivamente sin brechas, por lo que <= 3 selecciona exactamente los tres totales distintos más altos y cada producto que pertenezca a ellos.
El ORDER BY final no afecta el rango; solo ordena las filas retornadas. Esta distinción importa: el ordenamiento dentro de la ventana define el rango de negocio, mientras que el ordenamiento al final define la presentación reproducible. Pueden usar claves distintas.
Paso 6: Vincular las afirmaciones de rendimiento a la evidencia
Si R artículos de línea elegibles se convierten en G grupos categoría-producto, el trabajo lógico escanea y agrega R filas, luego ordena G filas dentro de las categorías. El ordenamiento puede describirse como la suma de G_c log G_c a través de las particiones de categoría, pero PostgreSQL puede elegir hash aggregation, sort aggregation, ejecución paralela o desbordamiento a disco. Esa expresión no es una promesa sobre el plan físico.
Usa EXPLAIN (ANALYZE, BUFFERS) en una réplica a escala de producción para inspeccionar las filas filtradas, la estrategia de join, las filas agregadas, la memoria de ordenamiento y los archivos temporales. orders(status, ordered_at, order_id) y order_items(order_id) pueden ayudar al filtrado y al join, dependiendo de la selectividad del estatus, la distribución y el particionamiento. El particionamiento temporal puede podar una tabla de hechos grande. Para un reporte frecuente, considera un agregado diario de categoría-producto reconciliado. La preaggregación intercambia frescura, backfill y complejidad de corrección por velocidad de consulta; "crear una vista materializada" no es un diseño completo.
Paso 7: Demostrar la semántica con datos pequeños y el costo con datos grandes
Un fixture de correctitud mínimo incluye la categoría 10 con ingresos 100, 100, 90, 80, 70; espera los primeros cuatro productos con rangos 1, 1, 2, 3. La categoría 20 tiene solo 50, 40; espera ambos. Incluye una orden cancelada, que no debe contribuir, y una orden exactamente en 2026-07-01 00:00:00+00, que debe ser excluida.
Agrega varias órdenes y artículos de línea para el mismo producto para asegurarte de que produzca un total. Asigna a diferentes IDs de producto el mismo total para verificar un orden final estable pero con el mismo rango. La validación a escala verifica entonces las particiones escaneadas, la cardinalidad del agregado, el desbordamiento del ordenamiento, el tiempo transcurrido y los recursos máximos. La salida correcta y el costo aceptable son cuerpos de evidencia separados.
Respuesta de muestra de alta calidad
"Primero aclararía el comportamiento de empates, la atribución temporal y el estatus elegible. El requisito es los tres niveles de ingresos distintos más altos por categoría con cada empate del tercer nivel, por lo que usaría DENSE_RANK; usaría ROW_NUMBER solo para exactamente tres filas.
El grano objetivo es una fila por categoría y producto, mientras que el grano de origen es un artículo de orden. Mi primer CTE, por lo tanto, une orders con items, conserva las órdenes COMPLETED en UTC Q2 y agrega SUM(quantity * unit_price) por category_id, product_id. Uso el 1 de abril inclusive y el 1 de julio exclusive para que los trimestres adyacentes no se superpongan.
El segundo CTE aplica DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC). No agrego product_id a esa ventana porque dividiría los pares con ingresos iguales. PostgreSQL no puede filtrar un resultado de ventana en la cláusula WHERE del mismo nivel de consulta, por lo que la consulta exterior aplica revenue_rank <= 3 y luego ordena por categoría, rango e ID de producto para una visualización estable.
Los ingresos 100, 100, 90, 80 se convierten en rangos 1, 1, 2, 3, por lo que los cuatro productos se retornan. También probaría órdenes canceladas, el límite derecho del trimestre, artículos de línea repetidos para un producto y categorías con menos de tres niveles. A escala, mediría cuánto reducen el filtrado y la agregación las R filas fuente antes de usar el plan de ejecución para justificar la poda de particiones, índices, ajuste de desbordamiento o preaggregación."
Esta respuesta establece la semántica de negocio antes que la sintaxis, luego provee la estructura de la consulta, evidencia de correctitud y una ruta de rendimiento medida. No trata un nombre de función ni una sugerencia de índice sin probar como la respuesta.
Errores comunes
- Rankear artículos de línea directamente → un producto ocupa varias posiciones y el grano de salida es incorrecto → agrega por categoría y producto antes de rankear.
- Usar
ORDER BY ... LIMIT 3global → retorna tres filas para todo el conjunto de datos → particiona el ranking porcategory_id. - Elegir
ROW_NUMBERsin un contrato de empates → los productos empatados pueden cortarse arbitrariamente → define primero tres filas, posiciones de competencia o tres valores distintos. - Agregar
product_idal ordenamiento deDENSE_RANK→ los ingresos iguales dejan de ser pares → rankea solo con claves de rango de negocio y estabiliza la presentación en elORDER BYfinal. - Filtrar
revenue_ranken el mismoWHERE→ el resultado de ventana no existe en esa etapa lógica → rankea en un CTE o subconsulta y filtra afuera. - Terminar el trimestre el 30 de junio a las 23:59:59 → puede omitirse mayor precisión de timestamp y los períodos adyacentes resultan incómodos → usa
[start, next_start). - Unir la dimensión de categoría actual sin semántica histórica → la recategorización de productos reescribe el historial → usa una instantánea en el momento de la orden o una dimensión con fechas efectivas y declara el contrato.
- Ofrecer solo un índice → la baja selectividad puede hacerlo inútil, y no puede corregir un grano incorrecto → mide cardinalidades, el plan, el desbordamiento y el cuello de botella real.
- Acumular dinero en punto flotante → el redondeo puede cambiar los grupos de empate → conserva un tipo numérico exacto y define las reglas de moneda e ingreso neto.
Preguntas de seguimiento y respuestas
Seguimiento 1: ¿Qué pasa si el producto requiere exactamente tres filas por categoría?
Usa ROW_NUMBER y define un orden secundario determinístico y explicable para ingresos iguales, como product_id ASC o una regla explícitamente solicitada de unidades vendidas o fecha de lanzamiento. Esa regla rompe deliberadamente los empates, por lo que pertenece al contrato de salida. Una categoría con menos de tres productos igualmente retorna menos filas a menos que se requieran explícitamente marcadores de posición.
Seguimiento 2: ¿Qué pasa si "los tres principales" usa ranking de competencia?
Usa RANK. Los ingresos 100, 100, 90, 80 reciben 1, 1, 3, 4, y filtrar en 3 retorna los niveles 100 y 90. Preserva la brecha después de un empate, lo que corresponde a "el siguiente producto es tercero". Eso es distinto a solicitar los tres valores distintos más altos con DENSE_RANK.
Seguimiento 3: ¿Se puede evitar el CTE y filtrar el rango en HAVING?
No puedes filtrar un resultado de ventana en la cláusula HAVING o WHERE del mismo nivel de consulta, porque las funciones de ventana son lógicamente posteriores. Una subconsulta equivalente funciona. Algunos almacenes de datos admiten QUALIFY, pero PostgreSQL 18 no. El CTE aquí hace explícitos los límites de agregación, ranking y filtrado.
Seguimiento 4: ¿Qué pasa si un reembolso ocurre en el siguiente trimestre?
Define primero el contrato de contabilidad. Un reporte de atribución de órdenes puede reestablecer el trimestre de la orden original; un reporte de flujo de caja puede registrar un evento negativo en el trimestre del reembolso. Esos requieren tiempos de evento y tablas de hechos distintos. No restes una tabla de reembolsos que carezca de semántica de importe y tiempo de evento; modela hechos de orden y reembolso trazables y declara la política de reestablecimiento o ajuste.
Seguimiento 5: ¿Cómo manejarías mil millones de artículos de línea trimestrales?
Poda las particiones de fecha y filtra el estatus antes de rankear, luego inspecciona los índices de clave de join, el paralelismo del agregado y el desbordamiento del ordenamiento. Si el reporte es frecuente y puede tolerar retraso, mantén un agregado diario exacto de categoría-producto y súmalo para el trimestre. Las órdenes tardías, los reembolsos y las correcciones necesitan backfill idempotente y reconciliación con los hechos crudos. Solo el plan de ejecución y una carga de trabajo representativa pueden elegir entre indexación, particionamiento y preaggregación.
Seguimiento 6: ¿Cómo detectas un join que duplicó los ingresos?
Verifica el grano y la conservación en cada etapa: si los conteos de filas en el join de artículos elegibles coinciden con la cardinalidad esperada, si orders.order_id es único y si la suma de los agregados de producto es igual a la suma de los importes de artículos filtrados. Agrega un contraejemplo con dos artículos en una orden y una clave de dimensión duplicada. Si una dimensión no es única, selecciona una versión con fecha efectiva antes de hacer el join; un DISTINCT final solo oculta la contribución duplicada.