Skip to content

Instantly share code, notes, and snippets.

@nfarah86
Created December 14, 2020 02:56
Show Gist options
  • Save nfarah86/d584770565a9ede40fb88f4e672b0b6b to your computer and use it in GitHub Desktop.
Save nfarah86/d584770565a9ede40fb88f4e672b0b6b to your computer and use it in GitHub Desktop.
Query that gets customers activity
SELECT
*
FROM
(
SELECT
c.ts AS event_date,
c.event,
c.type AS event_type,
c.result,
customer_id
FROM
commons.customer_events_marketing c
UNION ALL
SELECT
purchase_date,
'Store Visit' AS event,
'Purchase' AS event_type,
concat('$ ', cast(item_cost as varchar(100))) as result,
cast(customer_id AS VARCHAR) AS customer_id
FROM
commons.orders o
WHERE
o.is_refunded = False
UNION ALL
SELECT
purchase_date,
'Store Visit' AS event,
'Refund' AS event_type,
concat('$ -', cast(item_cost as varchar(100))) as result,
cast(customer_id AS VARCHAR) AS customer_id
FROM
commons.orders o
WHERE
o.is_refunded = True
) a
WHERE
customer_id = :customer_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment