Revenue By Customer

1-- All-time revenue per customer, enriched with the customer dimension (name, country, cohort).
2-- Demonstrates joining a managed fact view to the managed customer view, and a currency conversion
3-- to a chosen reporting currency (here EUR) from the ORIGINAL amount as charged.
4--
5-- Replace the two managed view names with the discovered ones:
6--   SELECT table_name FROM system.information_schema.tables WHERE table_name ILIKE '%revenue_analytics%';
7SELECT
8    c.id                                                          AS customer_id,
9    any(c.name)                                                   AS customer_name,
10    any(c.country)                                                AS country,
11    sum(ri.amount)                                                AS revenue_base_ccy,
12    sum(convertCurrency(ri.original_currency, 'EUR', ri.original_amount, ri.timestamp)) AS revenue_eur,
13    count(DISTINCT ri.subscription_id)                            AS subscriptions
14FROM <revenue_item_view> AS ri
15LEFT JOIN <customer_view> AS c ON ri.customer_id = c.id
16GROUP BY customer_id
17ORDER BY revenue_base_ccy DESC