-
Notifications
You must be signed in to change notification settings - Fork 6
Open
Description
Given the following query:
WITH customer_orders AS (
SELECT
o.customer_id,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.quantity * p.price) AS total_spent,
MIN(o.order_date) AS first_order_date,
MAX(o.order_date) AS last_order_date,
LIST(DISTINCT p.category) AS purchased_categories
FROM
starbake.orders o
JOIN
starbake.products p ON o.product_id = p.product_id
GROUP BY
o.customer_id
)
SELECT
co.customer_id,
concat(c.first_name,' ', c.last_name) AS customer_name,
c.email,
co.total_orders,
co.total_spent,
co.first_order_date,
co.last_order_date,
co.purchased_categories,
DATEDIFF('day', co.first_order_date, co.last_order_date) AS days_since_first_order
FROM
starbake.customers c
LEFT JOIN
customer_orders co ON c.id = co.customer_id
ORDER BY
co.total_spent DESC NULLS LAST;
and the following schema
---
version: 1
task:
name: "customer_purchase_history"
domain: "starbake_analytics"
table: "customer_purchase_history"
attributes:
- name: "customer_id"
type: "long"
comment: "Unique for each customer"
- name: "purchase_date"
type: "date"
comment: "Date of purchase"
I get the following schema diff
Table Attribute: customer_id: long, Status: UNCHANGED
Table Attribute: customer_name: Other, Status: ADD
Table Attribute: email: string, Status: ADD
Table Attribute: total_orders: Other, Status: ADD
Table Attribute: total_spent: Other, Status: ADD
Table Attribute: first_order_date: Other, Status: ADD
Table Attribute: last_order_date: Other, Status: ADD
Table Attribute: purchased_categories: Other, Status: ADD
Table Attribute: days_since_first_order: Other, Status: ADD
Table Attribute: purchase_date: date, Status: UNCHANGED
Metadata
Metadata
Assignees
Labels
No labels