click_revenue.sql

SELECT DATE_TRUNC('day', checkout.created) AS checkout_date,
       DATE_TRUNC('day', interaction.created) AS interaction_date,
       CASE WHEN DATEDIFF('days', interaction.created, checkout.created) <= 15 THEN checkout.value
       ELSE NULL END AS value,
       interaction.owner_pub_id AS pub_id,
       interaction.interaction_type AS interaction_type,
       checkout.auction_id
FROM (
    SELECT interaction.interaction_type AS checkout_type,
           interaction.owner_merch_id AS merch_id,
           interaction.checkout_local_value / 100.0 AS value,
           interaction.created AS created,
           interaction.auction_id AS auction_id,
           LAST_VALUE(
             CASE WHEN interaction.owner_merch_id is not NULL THEN NULL
             ELSE interaction.interaction_id END IGNORE NULLS
           )
           OVER (
               PARTITION BY event_context.bam_id
               ORDER BY interaction.created ROWS UNBOUNDED PRECEDING
           ) as previous_id
    FROM public.interaction AS interaction
    JOIN public.event_context AS event_context
        ON interaction.event_context_id = event_context.event_context_id
    WHERE interaction.created > '2017-03-01'
) AS checkout
JOIN interaction ON checkout.previous_id = interaction.interaction_id
WHERE checkout.value IS NOT NULL AND
      checkout.merch_id = 1000 AND
      pub_id IN (1003, 1007, 1027, 1075);