Hot Sale 2026: qué pasó con los precios

-- Agregado del hub (ventana 'hot-sale-2026') y línea de base. Sólo lectura. Pegable en psql.
-- Lee products_cache.category_id vivo: reproduce el agregado mientras las categorías
-- sean las de la generación. Si después cambió alguna, la lista congelada es `productos`.
set extra_float_digits = 1;  -- price es real: se lee por su texto más corto, como la app
with cats(clave, domain_id) as (values
    ('celulares', 'MLA-CELLPHONES'),
    ('notebooks', 'MLA-NOTEBOOKS'),
    ('tvs', 'MLA-TELEVISIONS'),
    ('tablets', 'MLA-TABLETS'),
    ('auriculares', 'MLA-HEADPHONES'),
    ('consolas', 'MLA-GAME_CONSOLES'),
    ('aires', 'MLA-AIR_CONDITIONERS'),
    ('electro_grande', 'MLA-REFRIGERATORS'),
    ('electro_grande', 'MLA-WASHING_MACHINES'),
    ('electro_grande', 'MLA-DRYER_MACHINES'),
    ('electro_grande', 'MLA-DISHWASHERS'),
    ('electro_grande', 'MLA-FREEZERS'),
    ('electro_grande', 'MLA-RANGES'),
    ('electro_grande', 'MLA-OVENS'),
    ('electro_grande', 'MLA-MICROWAVES'),
    ('electro_grande', 'MLA-COOKTOPS'),
    ('electro_grande', 'MLA-STORAGE_WATER_HEATERS'),
    ('electro_chico', 'MLA-AIR_FRYERS'),
    ('electro_chico', 'MLA-ELECTRIC_COFFEE_MAKERS'),
    ('electro_chico', 'MLA-VACUUM_AND_STEAM_CLEANERS'),
    ('electro_chico', 'MLA-ROBOT_VACUUMS'),
    ('electro_chico', 'MLA-ELECTRIC_JUGS'),
    ('electro_chico', 'MLA-BLENDERS'),
    ('electro_chico', 'MLA-MIXERS'),
    ('electro_chico', 'MLA-HAND_BLENDERS'),
    ('electro_chico', 'MLA-TEAPOTS_AND_KETTLES'),
    ('electro_chico', 'MLA-FANS'),
    ('electro_chico', 'MLA-ELECTRIC_HOME_HEATERS'),
    ('electro_chico', 'MLA-TOASTERS'),
    ('electro_chico', 'MLA-FOOD_PROCESSORS'),
    ('electro_chico', 'MLA-ELECTRIC_SQUEEZERS'),
    ('electro_chico', 'MLA-IRONS'),
    ('smartwatches', 'MLA-SMARTWATCHES'),
    ('monitores', 'MLA-COMPUTER_MONITORS')
),
ventanas(etiqueta, orden, ev_desde, ev_hasta, pre_desde, pre_hasta, san_desde, san_hasta) as (values
    ('hot-sale-2026', 0, date '2026-05-11', date '2026-05-13', date '2026-04-27', date '2026-05-10', date '2026-04-20', date '2026-06-10'),
    ('2026-06-08', 1, date '2026-06-08', date '2026-06-10', date '2026-05-25', date '2026-06-07', date '2026-05-18', date '2026-07-08'),
    ('2026-07-27', 2, date '2026-07-27', date '2026-07-29', date '2026-07-13', date '2026-07-26', date '2026-07-06', date '2026-08-26'),
    ('2026-08-03', 3, date '2026-08-03', date '2026-08-05', date '2026-07-20', date '2026-08-02', date '2026-07-13', date '2026-09-02')
),
con_evento as (
  select distinct ml_id from price_history
  where recorded_at >= '2026-05-11T03:00:00.000Z' and recorded_at < '2026-05-14T03:00:00.000Z'
),
dia as (
  select ml_id,
         (recorded_at::timestamptz at time zone 'America/Argentina/Buenos_Aires')::date as dia,
         min(price::text::float8) as p_min, max(price::text::float8) as p_max
  from price_history join con_evento using (ml_id)
  where recorded_at >= '2026-04-20T03:00:00.000Z' and recorded_at < '2026-09-03T03:00:00.000Z'
  group by 1, 2
),
ev as (
  select v.etiqueta, d.ml_id, count(*) as dias,
         round(min(d.p_min)::numeric, 2) as mn, round(max(d.p_max)::numeric, 2) as mx
  from ventanas v join dia d on d.dia between v.ev_desde and v.ev_hasta
  group by 1, 2
),
san as (
  select v.etiqueta, d.ml_id, round((percentile_cont(0.5) within group (order by d.p_min))::numeric, 2) as med
  from ventanas v join dia d
    on d.dia between v.san_desde and v.san_hasta and not (d.dia between v.ev_desde and v.ev_hasta)
  group by 1, 2
),
pre as (
  select v.etiqueta, d.ml_id, count(*) as n,
         round((percentile_cont(0.5) within group (order by d.p_min))::numeric, 2) as med
  from ventanas v join dia d on d.dia between v.pre_desde and v.pre_hasta
  group by 1, 2
),
base as (
  select ev.etiqueta, ev.ml_id, c.clave, ev.mn, coalesce(pre.n, 0) as pre_n, pre.med as pre_med
  from ev
  join products_cache p on p.ml_id = ev.ml_id
  join cats c on c.domain_id = p.category_id
  join san on san.etiqueta = ev.etiqueta and san.ml_id = ev.ml_id
  left join pre on pre.etiqueta = ev.etiqueta and pre.ml_id = ev.ml_id
  where ev.ml_id ~ '^MLA[0-9]{6,9}$'
    and ev.dias >= 2
    and ev.mn * 10 >= 6 * san.med
    and ev.mx * 10 <= 15 * san.med
),
elegibles as (select ml_id from base where etiqueta = 'hot-sale-2026')
select b.etiqueta, coalesce(b.clave, 'TOTAL') as clave,
       count(*) as n,
       count(*) filter (where pre_n >= 3) as n_comparables,
       count(*) filter (where pre_n >= 3 and mn * 100 < 97 * pre_med) as bajaron_mas_de_3,
       count(*) filter (where pre_n >= 3 and mn * 100 <= 90 * pre_med) as bajaron_10_o_mas
from base b join elegibles using (ml_id)
group by b.etiqueta, rollup(b.clave)
order by b.etiqueta, clave nulls last;