Skip to content

Instantly share code, notes, and snippets.

@bryanmtl
Created August 26, 2019 17:21
Show Gist options
  • Save bryanmtl/18577d0bf6ec2d8002bc2869fe9dd434 to your computer and use it in GitHub Desktop.
Save bryanmtl/18577d0bf6ec2d8002bc2869fe9dd434 to your computer and use it in GitHub Desktop.
Looker query
SELECT
customers.first_name AS "customers.first_name",
(COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(customers.total_spent ,0)*(1000000*1.0)) AS DECIMAL(65,0))) + ('x' || MD5(customers.id ::varchar))::bit(64)::bigint::DECIMAL(65,0) *18446744073709551616 + ('x' || SUBSTR(MD5(customers.id ::varchar),17))::bit(64)::bigint::DECIMAL(65,0) ) - SUM(DISTINCT ('x' || MD5(customers.id ::varchar))::bit(64)::bigint::DECIMAL(65,0) *18446744073709551616 + ('x' || SUBSTR(MD5(customers.id ::varchar),17))::bit(64)::bigint::DECIMAL(65,0)) ) AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) / NULLIF(COUNT(DISTINCT CASE WHEN customers.total_spent IS NOT NULL THEN customers.id ELSE NULL END), 0)) AS "customers.avg_spent"
FROM shopify.orders AS orders
LEFT JOIN shopify.customers AS customers ON customers.id = orders.customer__id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 500
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment