Query para armar dataset Laboratorio de Implementación II

query.sql

SELECT
  cliente.cid AS cliente_cid,
  cliente.monto_sucursal_1 AS cliente_monto_sucursal_1,
  cliente.monto_sucursal_2 AS cliente_monto_sucursal_2,
  cliente.monto_sucursal_3 AS cliente_monto_sucursal_3,
  cliente.monto_sucursal_4 AS cliente_monto_sucursal_4,
  cliente.promociones AS cliente_promociones,
  cliente.dias_cliente AS cliente_dias_cliente,
  cliente.sucursales AS cliente_sucursales,
  cliente.promos_enviadas AS cliente_promos_enviadas,
  cliente.promos_compras AS cliente_promos_compras,
  cliente.produni AS cliente_produni,
  cliente.flagret AS cliente_flagret,
  cliente.frecuencia_compra AS cliente_frecuencia_compra,
  cliente.estado AS cliente_estado,
  cliente.county AS cliente_county,
  cliente.mid AS cliente_mid,
  cliente.fecha_nacimiento AS cliente_fecha_nacimiento,
  geomarketing.p_una_unidad AS geomarketing_p_una_unidad,
  geomarketing.p_20_unidades AS geomarketing_p_20_unidades,
  geomarketing.p_casas_nuevas AS geomarketing_p_casas_nuevas,
  geomarketing.p_casas_viejas AS geomarketing_p_casas_viejas,
  geomarketing.p_3_autos AS geomarketing_p_3_autos,
  geomarketing.p_auto AS geomarketing_p_auto,
  geomarketing.p_transporte_publico AS geomarketing_p_transporte_publico,
  geomarketing.p_camina AS geomarketing_p_camina,
  geomarketing.p_posgrado AS geomarketing_p_posgrado,
  geomarketing.p_nativos AS geomarketing_p_nativos,
  geomarketing.p_extranjeros AS geomarketing_p_extranjeros,
  geomarketing.p_ciudadanos AS geomarketing_p_ciudadanos,
  COALESCE(campana_200804.flag_incluido, 0) AS campana_200804_flag_incluido,
  COALESCE(campana_200804.flag_compro, 0) AS campana_200804_flag_compro,
  COALESCE(sv_200604_200710.mes_ultima_compra, 0) AS sv_200604_200710_mes_ultima_compra,
  COALESCE(sv_200604_200710.monto, 0) AS sv_200604_200710_monto,
  COALESCE(sv_200604_200710.tipo_pago, 0) AS sv_200604_200710_tipo_pago,
  COALESCE(sv_200604_200710.canal, 0) AS sv_200604_200710_canal,
  COALESCE(sv_200604_200710.compras, 0) AS sv_200604_200710_compras,
  COALESCE(sv_200604_200710.classes, 0) AS sv_200604_200710_classes,
  COALESCE(sv_200604_200710.coupons, 0) AS sv_200604_200710_coupons,
  COALESCE(sv_200604_200710.items, 0) AS sv_200604_200710_items,
  COALESCE(monto_200705.monto, 0) AS monto_200705_monto,
  COALESCE(svm_200711.monto, 0) AS svm_200711_monto,
  COALESCE(svm_200711.tipo_pago, 0) AS svm_200711_tipo_pago,
  COALESCE(svm_200711.canal, 0) AS svm_200711_canal,
  COALESCE(svm_200711.compras, 0) AS svm_200711_compras,
  COALESCE(svm_200711.classes, 0) AS svm_200711_classes,
  COALESCE(svm_200711.coupons, 0) AS svm_200711_coupons,
  COALESCE(svm_200711.items, 0) AS svm_200711_items,
  COALESCE(svm_200712.monto, 0) AS svm_200712_monto,
  COALESCE(svm_200712.tipo_pago, 0) AS svm_200712_tipo_pago,
  COALESCE(svm_200712.canal, 0) AS svm_200712_canal,
  COALESCE(svm_200712.compras, 0) AS svm_200712_compras,
  COALESCE(svm_200712.classes, 0) AS svm_200712_classes,
  COALESCE(svm_200712.coupons, 0) AS svm_200712_coupons,
  COALESCE(svm_200712.items, 0) AS svm_200712_items,
  COALESCE(svm_200801.monto, 0) AS svm_200801_monto,
  COALESCE(svm_200801.tipo_pago, 0) AS svm_200801_tipo_pago,
  COALESCE(svm_200801.canal, 0) AS svm_200801_canal,
  COALESCE(svm_200801.compras, 0) AS svm_200801_compras,
  COALESCE(svm_200801.classes, 0) AS svm_200801_classes,
  COALESCE(svm_200801.coupons, 0) AS svm_200801_coupons,
  COALESCE(svm_200801.items, 0) AS svm_200801_items,
  COALESCE(svm_200802.monto, 0) AS svm_200802_monto,
  COALESCE(svm_200802.tipo_pago, 0) AS svm_200802_tipo_pago,
  COALESCE(svm_200802.canal, 0) AS svm_200802_canal,
  COALESCE(svm_200802.compras, 0) AS svm_200802_compras,
  COALESCE(svm_200802.classes, 0) AS svm_200802_classes,
  COALESCE(svm_200802.coupons, 0) AS svm_200802_coupons,
  COALESCE(svm_200802.items, 0) AS svm_200802_items,
  COALESCE(svm_200803.monto, 0) AS svm_200803_monto,
  COALESCE(svm_200803.tipo_pago, 0) AS svm_200803_tipo_pago,
  COALESCE(svm_200803.canal, 0) AS svm_200803_canal,
  COALESCE(svm_200803.compras, 0) AS svm_200803_compras,
  COALESCE(svm_200803.classes, 0) AS svm_200803_classes,
  COALESCE(svm_200803.coupons, 0) AS svm_200803_coupons,
  COALESCE(svm_200803.items, 0) AS svm_200803_items,
  COALESCE(svm_200804.monto, 0) AS svm_200804_monto,
  COALESCE(svm_200804.tipo_pago, 0) AS svm_200804_tipo_pago,
  COALESCE(svm_200804.canal, 0) AS svm_200804_canal,
  COALESCE(svm_200804.compras, 0) AS svm_200804_compras,
  COALESCE(svm_200804.classes, 0) AS svm_200804_classes,
  COALESCE(svm_200804.coupons, 0) AS svm_200804_coupons,
  COALESCE(svm_200804.items, 0) AS svm_200804_items,
  COALESCE(svm_200805.monto, 0) AS svm_200805_monto,
  COALESCE(svm_200805.tipo_pago, 0) AS svm_200805_tipo_pago,
  COALESCE(svm_200805.canal, 0) AS svm_200805_canal,
  COALESCE(svm_200805.compras, 0) AS svm_200805_compras,
  COALESCE(svm_200805.classes, 0) AS svm_200805_classes,
  COALESCE(svm_200805.coupons, 0) AS svm_200805_coupons,
  COALESCE(svm_200805.items, 0) AS svm_200805_items,
  COALESCE(svm_200806.monto, 0) AS svm_200806_monto,
  COALESCE(svm_200806.tipo_pago, 0) AS svm_200806_tipo_pago,
  COALESCE(svm_200806.canal, 0) AS svm_200806_canal,
  COALESCE(svm_200806.compras, 0) AS svm_200806_compras,
  COALESCE(svm_200806.classes, 0) AS svm_200806_classes,
  COALESCE(svm_200806.coupons, 0) AS svm_200806_coupons,
  COALESCE(svm_200806.items, 0) AS svm_200806_items,
  (
    COALESCE(sv_200604_200710.monto, 0) +
    COALESCE(monto_200705.monto, 0) +
    COALESCE(svm_200711.monto, 0) +
    COALESCE(svm_200712.monto, 0) +
    COALESCE(svm_200801.monto, 0) +
    COALESCE(svm_200802.monto, 0) +
    COALESCE(svm_200803.monto, 0) +
    COALESCE(svm_200804.monto, 0) +
    COALESCE(svm_200805.monto, 0) +
    COALESCE(svm_200806.monto, 0)
  ) AS cliente_monto_total_historico,
  ROUND(cliente.promociones * 1.0 / cliente.dias_cliente, 4) AS cliente_part_en_promo,
  ROUND(cliente.promos_compras * 1.0 / cliente.promos_enviadas, 4) AS cliente_comp_env,
  ROUND(cliente.frecuencia_compra * 1.0 / cliente.dias_cliente, 4) AS cliente_frec_dias_hist,
  (cliente.estado || '-' || cliente.county) AS cliente_estado_county,
  CAST(SUBSTR(mid, LENGTH(mid) - 1, 2) AS text) AS cliente_segmento,
  ((strftime('%Y', 'now') - strftime('%Y', fecha_nacimiento)) - (strftime('%m-%d', 'now') < strftime('%m-%d', fecha_nacimiento))) AS cliente_edad,
  (
    COALESCE(cliente.monto_sucursal_1, 0) / (
      COALESCE(sv_200604_200710.monto, 0) +
      COALESCE(monto_200705.monto, 0) +
      COALESCE(svm_200711.monto, 0) +
      COALESCE(svm_200712.monto, 0) +
      COALESCE(svm_200801.monto, 0) +
      COALESCE(svm_200802.monto, 0) +
      COALESCE(svm_200803.monto, 0) +
      COALESCE(svm_200804.monto, 0) +
      COALESCE(svm_200805.monto, 0) +
      COALESCE(svm_200806.monto, 0)
    )
  ) AS cliente_monto_suc_1_total,
  (
    COALESCE(cliente.monto_sucursal_2, 0) / (
      COALESCE(sv_200604_200710.monto, 0) +
      COALESCE(monto_200705.monto, 0) +
      COALESCE(svm_200711.monto, 0) +
      COALESCE(svm_200712.monto, 0) +
      COALESCE(svm_200801.monto, 0) +
      COALESCE(svm_200802.monto, 0) +
      COALESCE(svm_200803.monto, 0) +
      COALESCE(svm_200804.monto, 0) +
      COALESCE(svm_200805.monto, 0) +
      COALESCE(svm_200806.monto, 0)
    )
  ) AS cliente_monto_suc_2_total,
  (
    COALESCE(cliente.monto_sucursal_3, 0) / (
      COALESCE(sv_200604_200710.monto, 0) +
      COALESCE(monto_200705.monto, 0) +
      COALESCE(svm_200711.monto, 0) +
      COALESCE(svm_200712.monto, 0) +
      COALESCE(svm_200801.monto, 0) +
      COALESCE(svm_200802.monto, 0) +
      COALESCE(svm_200803.monto, 0) +
      COALESCE(svm_200804.monto, 0) +
      COALESCE(svm_200805.monto, 0) +
      COALESCE(svm_200806.monto, 0)
    )
  ) AS cliente_monto_suc_3_total,
  (
    COALESCE(cliente.monto_sucursal_4, 0) / (
      COALESCE(sv_200604_200710.monto, 0) +
      COALESCE(monto_200705.monto, 0) +
      COALESCE(svm_200711.monto, 0) +
      COALESCE(svm_200712.monto, 0) +
      COALESCE(svm_200801.monto, 0) +
      COALESCE(svm_200802.monto, 0) +
      COALESCE(svm_200803.monto, 0) +
      COALESCE(svm_200804.monto, 0) +
      COALESCE(svm_200805.monto, 0) +
      COALESCE(svm_200806.monto, 0)
    )
  ) AS cliente_monto_suc_4_total,
  (
    (
      COALESCE(sv_200604_200710.coupons, 0) + COALESCE(svm_200711.coupons, 0) + COALESCE(svm_200712.coupons, 0) + COALESCE(svm_200801.coupons, 0) + COALESCE(svm_200802.coupons, 0) + COALESCE(svm_200803.coupons, 0) + COALESCE(svm_200804.coupons, 0) + COALESCE(svm_200805.coupons, 0) + COALESCE(svm_200806.coupons, 0)
    ) * 1.0 / (
      COALESCE(sv_200604_200710.items, 0) + COALESCE(svm_200711.items, 0) + COALESCE(svm_200712.items, 0) + COALESCE(svm_200801.items, 0) + COALESCE(svm_200802.items, 0) + COALESCE(svm_200803.items, 0) + COALESCE(svm_200804.items, 0) + COALESCE(svm_200805.items, 0) + COALESCE(svm_200806.items, 0)
    )
  ) AS cliente_coupons_over_items,
  COALESCE((
    (
      (
        0.8 * COALESCE(svm_200806.coupons, 0) + 0.6 * COALESCE(svm_200805.monto, 0) + 0.3 * COALESCE(svm_200804.monto, 0) + 0.2 * COALESCE(svm_200803.monto, 0) + 0.1 * COALESCE(svm_200802.monto, 0)
      ) - (
        (0.8 + 0.6 + 0.3 + 0.2 + 0.1) * ((COALESCE(svm_200806.coupons, 0) + COALESCE(svm_200805.monto, 0) + COALESCE(svm_200804.monto, 0) + COALESCE(svm_200803.monto, 0) + COALESCE(svm_200802.monto, 0)) / 5)
      )
    ) / ((COALESCE(svm_200806.coupons, 0) + COALESCE(svm_200805.monto, 0) + COALESCE(svm_200804.monto, 0) + COALESCE(svm_200803.monto, 0) + COALESCE(svm_200802.monto, 0)) / 5)), 0) AS tendencia_monto
  FROM cliente
LEFT JOIN geomarketing ON (cliente.county = geomarketing.county AND cliente.estado = geomarketing.estado)
LEFT JOIN campana_200804 ON (campana_200804.cid = cliente.cid)
LEFT JOIN sv_200604_200710 ON (sv_200604_200710.cid = cliente.cid)
LEFT JOIN monto_200705 ON (monto_200705.cid = cliente.cid)
LEFT JOIN svm_200711_200806 AS svm_200711 ON (cliente.cid = svm_200711.cid AND svm_200711.mes = 200711)
LEFT JOIN svm_200711_200806 AS svm_200712 ON (cliente.cid = svm_200712.cid AND svm_200712.mes = 200712)
LEFT JOIN svm_200711_200806 AS svm_200801 ON (cliente.cid = svm_200801.cid AND svm_200801.mes = 200801)
LEFT JOIN svm_200711_200806 AS svm_200802 ON (cliente.cid = svm_200802.cid AND svm_200802.mes = 200802)
LEFT JOIN svm_200711_200806 AS svm_200803 ON (cliente.cid = svm_200803.cid AND svm_200803.mes = 200803)
LEFT JOIN svm_200711_200806 AS svm_200804 ON (cliente.cid = svm_200804.cid AND svm_200804.mes = 200804)
LEFT JOIN svm_200711_200806 AS svm_200805 ON (cliente.cid = svm_200805.cid AND svm_200805.mes = 200805)
LEFT JOIN svm_200711_200806 AS svm_200806 ON (cliente.cid = svm_200806.cid AND svm_200806.mes = 200806);