La solicitud
De Thomas Whitaker, Director de Merchandising
“Los ingresos mensuales por categoría saltan demasiado para planificar las compras. Para 2025, dame los ingresos mensuales de cada categoría con una media móvil de tres meses al lado, y asegúrate de que enero y febrero incluyan el final de 2024.”
Tu objetivo
Usando pedidos completados y líneas con quantity > 0, calcula los ingresos netos (suma de quantity * unit_price * (1 - discount_pct / 100.0)) por categoría y por mes para todos los meses. Añade revenue_3m_avg: el promedio de los ingresos mensuales de esa categoría en el mes y los 2 meses anteriores. Devuelve solo los meses de 2025 con: category, month (primer día del mes, como DATE), net_revenue y revenue_3m_avg (ambos redondeados a 2). Ordena por category y luego por month.
- Lee de la tabla
productspendiente - Lee de la tabla
orderspendiente - Muestra exactamente
categorymonthnet_revenuerevenue_3m_avgpendiente
Tu consulta
Cargando el editor…
Solución de referencia
| product_id | name | category | subcategory | unit_price |
|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 |
Mote revisa tu resultado
Presiona Ejecutar. Cada fila que cumple el objetivo se vuelve una luciérnaga en tu linterna.
Lo que recibe Thomas, y por qué
Tablas
Datos de la tienda Harbor & Pine, listos para consultar.
products120 filas · 8 columnas
product_idintegernamevarcharcategoryvarcharsubcategoryvarcharunit_pricedecimalunit_costdecimallaunched_ondateis_activeboolean
| product_id | name | category | subcategory | unit_price | unit_cost | launched_on | is_active |
|---|---|---|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 | NULL | 2023-09-11 | true |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 | 503.50 | 2024-05-16 | true |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 | 429.87 | 2023-05-27 | true |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 | 214.70 | 2022-12-20 | true |
| 105 | Avery Leather Bed Frame | Furniture | Beds | 449.00 | 232.70 | 2021-08-31 | true |
orders12,000 filas · 7 columnas
order_idintegercustomer_idintegerorder_datetimestampstatusvarcharchannelvarcharshipping_feedecimalcoupon_codevarchar
| order_id | customer_id | order_date | status | channel | shipping_fee | coupon_code |
|---|---|---|---|---|---|---|
| 1001 | 125 | 2023-01-01 20:51:19 | completed | web | 0.00 | NULL |
| 1002 | 156 | 2023-01-02 07:58:46 | completed | web | 0.00 | NULL |
| 1003 | 22 | 2023-01-02 10:25:27 | completed | marketplace | 4.99 | WELCOME10 |
| 1004 | 48 | 2023-01-02 11:21:51 | completed | marketplace | 4.99 | WELCOME10 |
| 1005 | 82 | 2023-01-02 11:43:36 | completed | web | 0.00 | WELCOME10 |
order_items30,338 filas · 6 columnas
order_item_idintegerorder_idintegerproduct_idintegerquantityintegerunit_pricedecimaldiscount_pctdecimal
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct |
|---|---|---|---|---|---|
| 1 | 1001 | 121 | 9 | 31.99 | 0.00 |
| 2 | 1001 | 154 | 3 | 11.99 | 0.00 |
| 3 | 1002 | 154 | 4 | 11.99 | 0.00 |
| 4 | 1002 | 194 | 1 | 149.00 | 0.00 |
| 5 | 1002 | 193 | 2 | 19.99 | 0.00 |
customers2,018 filas · 9 columnas
customer_idintegerfirst_namevarcharlast_namevarcharemailvarcharcityvarcharstatevarcharsignup_datedatesegmentvarcharreferral_sourcevarchar
| customer_id | first_name | last_name | city | state | signup_date | segment | referral_source | |
|---|---|---|---|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco | CA | 2022-06-02 | consumer | search |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta | GA | 2022-06-02 | consumer | NULL |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles | CA | 2022-06-04 | business | NULL |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle | WA | 2022-06-06 | business | |
| 5 | Kyle | Jimenez | kjimenez27@yahoo.com | Indianapolis | IN | 2022-06-07 | consumer | NULL |
returns1,673 filas · 5 columnas
return_idintegerorder_item_idintegerreturned_ondatereasonvarcharrefund_amountdecimal
| return_id | order_item_id | returned_on | reason | refund_amount |
|---|---|---|---|---|
| 1 | 14 | 2023-01-10 | damaged | 83.69 |
| 2 | 42 | 2023-01-10 | wrong_item | 19.99 |
| 3 | 105 | 2023-01-14 | wrong_item | 10.99 |
| 4 | 24 | 2023-01-16 | changed_mind | 26.99 |
| 5 | 126 | 2023-01-16 | late | 20.99 |
employees60 filas · 8 columnas
employee_idintegerfirst_namevarcharlast_namevarchardepartmentvarchartitlevarcharmanager_idintegerhire_datedatesalaryinteger
| employee_id | first_name | last_name | department | title | manager_id | hire_date | salary |
|---|---|---|---|---|---|---|---|
| 1 | Catherine | Hale | Executive | Chief Executive Officer | NULL | 2016-03-01 | 285000 |
| 2 | Robert | Kim | Executive | Chief Operating Officer | 1 | 2017-01-09 | 235000 |
| 3 | Megan | Collins | Sales | Head of Sales | 1 | 2018-04-16 | 172000 |
| 4 | Chloe | Gutierrez | Sales | Account Executive | 3 | 2020-01-21 | 72000 |
| 5 | Frank | Bell | Sales | Account Executive | 3 | 2023-05-21 | 73000 |
web_sessions40,000 filas · 7 columnas
session_idintegercustomer_idintegerstarted_attimestampdevicevarcharsourcevarcharpages_viewedintegerconvertedboolean
| session_id | customer_id | started_at | device | source | pages_viewed | converted |
|---|---|---|---|---|---|---|
| 1 | NULL | 2023-01-01 23:02:24 | mobile | paid | 1 | false |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid | 1 | false |
| 3 | NULL | 2023-01-01 16:55:46 | desktop | direct | 7 | false |
| 4 | NULL | 2023-01-01 12:43:28 | mobile | organic | 3 | false |
| 5 | NULL | 2023-01-01 13:06:21 | mobile | social | 2 | false |
marketing_spend3,276 filas · 3 columnas
spend_datedatechannelvarcharamountdecimal
| spend_date | channel | amount |
|---|---|---|
| 2023-01-01 | paid | 492.99 |
| 2023-01-01 | 37.38 | |
| 2023-01-01 | social | 200.53 |
| 2023-01-02 | paid | 709.13 |
| 2023-01-02 | 64.13 |
La lección
Una media móvil sustituye cada valor por el promedio de una pequeña ventana a su alrededor, lo que suaviza el ruido y conserva la tendencia. El marco de la ventana dice exactamente qué filas se incluyen.
Repaso: el marco
AVG(net_revenue) OVER (
PARTITION BY category
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)PARTITION BY category: cada categoría se promedia por separado.ORDER BY month: “anterior” significa meses anteriores.ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: esta fila y las dos anteriores, tres meses en total. Para una ventana más larga, cambia el 2 (11 PRECEDING da un promedio de 12 meses).
ROWS cuenta filas, no meses, así que solo es una ventana de tres meses real cuando no falta ningún mes.
Todas las categorías de aquí tienen los 36 meses de 2023 a 2025; con huecos harías primero un join con un
calendario.
Filtra después de la ventana
Las ventanas solo ven las filas que quedan después del WHERE. Aquí está Outdoor, calculado de las dos formas:
| month | net_revenue | 3m avg (correct) | 3m avg (filtered first) |
|---|---|---|---|
| 2025-01 | 21,520.57 | 21,158.42 | 21,520.57 |
| 2025-02 | 7,369.74 | 16,602.84 | 14,445.16 |
| 2025-03 | 38,755.54 | 22,548.62 | 22,548.62 |
Desde marzo ambos coinciden, porque la ventana ya no llega hasta 2024. Los dos primeros meses salen mal sin avisar cuando el filtro se ejecuta primero. Así que: calcula la ventana en una CTE sobre todo el historial y conserva 2025 en la consulta externa.
La tabla también muestra por qué merchandising quiere suavizado: los ingresos de Outdoor cayeron a $7,369.74 en febrero de 2025 y luego saltaron a $38,755.54 en marzo al empezar la temporada.
Errores comunes
- Sin PARTITION BY. El enero de 2025 de Furniture promediaría los últimos meses de Decor.
- RANGE en lugar de ROWS con meses duplicados da marcos distintos; ROWS es la opción predecible.
- Redondear antes de promediar. Redondea solo la salida final.
En SQL Server
La misma sintaxis de marco funciona: AVG(net_revenue) OVER (PARTITION BY category ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). Ten en cuenta que AVG de una columna entera devuelve un entero en
SQL Server, así que convierte primero a decimal al promediar conteos.
¡Linterna encendida!
Practicaste: moving averagewindow framerows betweenpartition bycte
- Resuelto
- Sin pistas
- Al primer intento