What Is Malloy
Foreign Sums and Averages
Data often comes in a nested structure, where information is organized hierarchically. BigQuery and DuckDB have built-in support for reading tables with nested data and extracting information from these nested structures.
Working with nested data in Malloy is remarkably simple. In Malloy, a nested array or struct is treated as a built-in join_many operation. You can effortlessly access the desired data using dot notation.
For example, in Google Analytics data, the top level object is sessions. There are repeated structures such as hits, pageviews, and products and more. Querying this data in SQL is difficult.
Below is the partial schema for Google Analytics.
To perform aggregate calculations in Malloy, you can simply specify the complete path to the numeric value and select the appropriate aggregate function. Malloy refers to this as aggregate locality, ensuring accurate calculations regardless of the join pattern used.
A simple Google Analytics Semantic model
Here is a very simple Malloy model describing some interesting calculations on Google Analytics data:
source: ga_sessions is duckdb.table('../data/ga_sample.parquet') extend { measure: user_count is count(fullVisitorId) # percent percent_of_users is user_count / all(user_count) session_count is count() total_visits is totals.visits.sum() total_hits is totals.hits.sum() total_page_views is totals.pageviews.sum() t2 is totals.pageviews.sum() total_product_revenue is hits.product.productRevenue.sum() hits_count is hits.count() sold_count is hits.count() { where: hits.product.productQuantity > 0 } }
Show Data by Traffic Source
run: ga_sessions -> { where: trafficSource.`source` != '(direct)' group_by: trafficSource.`source` aggregate: user_count percent_of_users hits_count total_visits session_count limit: 10 }
| source | user_count | percent_of_users | hits_count | total_visits | session_count |
|---|---|---|---|---|---|
| youtube.com | 178 | 48.5% | 328 | 180 | 180 |
| analytics.google.com | 53 | 14.44% | 114 | 57 | 57 |
| Partners | 49 | 13.35% | 149 | 52 | 52 |
| google.com | 11 | 3% | 35 | 12 | 12 |
| dfa | 8 | 2.18% | 64 | 15 | 15 |
| sites.google.com | 8 | 2.18% | 34 | 8 | 8 |
| facebook.com | 7 | 1.91% | 36 | 7 | 7 |
| quora.com | 5 | 1.36% | 12 | 6 | 6 |
| baidu | 5 | 1.36% | 10 | 5 | 5 |
| reddit.com | 4 | 1.09% | 14 | 4 | 4 |
[ { "source": "youtube.com", "user_count": 178, "percent_of_users": 0.48501362397820164, "hits_count": 328, "total_visits": 180, "session_count": 180 }, { "source": "analytics.google.com", "user_count": 53, "percent_of_users": 0.1444141689373297, "hits_count": 114, "total_visits": 57, "session_count": 57 }, { "source": "Partners", "user_count": 49, "percent_of_users": 0.1335149863760218, "hits_count": 149, "total_visits": 52, "session_count": 52 }, { "source": "google.com", "user_count": 11, "percent_of_users": 0.02997275204359673, "hits_count": 35, "total_visits": 12, "session_count": 12 }, { "source": "dfa", "user_count": 8, "percent_of_users": 0.021798365122615803, "hits_count": 64, "total_visits": 15, "session_count": 15 }, { "source": "sites.google.com", "user_count": 8, "percent_of_users": 0.021798365122615803, "hits_count": 34, "total_visits": 8, "session_count": 8 }, { "source": "facebook.com", "user_count": 7, "percent_of_users": 0.01907356948228883, "hits_count": 36, "total_visits": 7, "session_count": 7 }, { "source": "quora.com", "user_count": 5, "percent_of_users": 0.013623978201634877, "hits_count": 12, "total_visits": 6, "session_count": 6 }, { "source": "baidu", "user_count": 5, "percent_of_users": 0.013623978201634877, "hits_count": 10, "total_visits": 5, "session_count": 5 }, { "source": "reddit.com", "user_count": 4, "percent_of_users": 0.010899182561307902, "hits_count": 14, "total_visits": 4, "session_count": 4 } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN ga_sessions.trafficSource."source" END as "source__1", CASE WHEN group_set=1 THEN count(distinct ga_sessions."fullVisitorId") END as "user_count__1", (CASE WHEN group_set=1 THEN count(distinct ga_sessions."fullVisitorId") END)*1.0/MAX((CASE WHEN group_set=0 THEN count(distinct ga_sessions."fullVisitorId") END)) OVER () as "percent_of_users__1", CASE WHEN group_set=1 THEN COUNT(DISTINCT ga_sessions."__distinct_key" || 'x' || hits_0_outer.__row_id) END as "hits_count__1", CASE WHEN group_set=1 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."visits"})) a ) ),0) END as "total_visits__1", CASE WHEN group_set=1 THEN COUNT(DISTINCT ga_sessions."__distinct_key") END as "session_count__1" FROM (SELECT GEN_RANDOM_UUID() as __distinct_key, x.* FROM '../data/ga_sample.parquet' as x) as ga_sessions LEFT JOIN LATERAL (SELECT UNNEST(GENERATE_SERIES(1, length(ga_sessions."hits"),1)) as __row_id, UNNEST(ga_sessions."hits"), 1 as ignoreme) as hits_0_outer(__row_id, hits_0,ignoreme) ON hits_0_outer.ignoreme=1 CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set WHERE COALESCE(NOT(ga_sessions.trafficSource."source"='(direct)'),FALSE) GROUP BY 1,2 ) SELECT "source__1" as "source", MAX(CASE WHEN group_set=1 THEN "user_count__1" END) as "user_count", MAX(CASE WHEN group_set=1 THEN "percent_of_users__1" END) as "percent_of_users", MAX(CASE WHEN group_set=1 THEN "hits_count__1" END) as "hits_count", MAX(CASE WHEN group_set=1 THEN "total_visits__1" END) as "total_visits", MAX(CASE WHEN group_set=1 THEN "session_count__1" END) as "session_count" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 10
Show Data By Browser
run: ga_sessions -> { group_by: device.browser aggregate: user_count percent_of_users total_visits total_hits total_page_views sold_count }
| browser | user_count | percent_of_users | total_visits | total_hits | total_page_views | sold_count |
|---|---|---|---|---|---|---|
| Chrome | 1,689 | 73.66% | 1,900 | 10,896 | 8,956 | 642 |
| Safari | 364 | 15.87% | 397 | 1,260 | 1,137 | 24 |
| Firefox | 95 | 4.14% | 101 | 390 | 343 | 10 |
| Internet Explorer | 50 | 2.18% | 54 | 107 | 102 | 1 |
| Edge | 23 | 1% | 23 | 63 | 55 | 0 |
| Opera Mini | 21 | 0.92% | 21 | 29 | 28 | 0 |
| Android Webview | 17 | 0.74% | 19 | 363 | 211 | 1 |
| Opera | 11 | 0.48% | 16 | 55 | 49 | 1 |
| Safari (in-app) | 8 | 0.35% | 10 | 33 | 29 | 1 |
| UC Browser | 6 | 0.26% | 6 | 22 | 14 | 0 |
| Android Browser | 2 | 0.09% | 2 | 3 | 3 | 0 |
| Nokia Browser | 2 | 0.09% | 2 | 3 | 3 | 0 |
| Coc Coc | 2 | 0.09% | 2 | 2 | 2 | 0 |
| YaBrowser | 2 | 0.09% | 2 | 6 | 6 | 0 |
| Mozilla Compatible Agent | 1 | 0.04% | 1 | 1 | 1 | 0 |
[ { "browser": "Chrome", "user_count": 1689, "percent_of_users": 0.7365896205843873, "total_visits": 1900, "total_hits": 10896, "total_page_views": 8956, "sold_count": 642 }, { "browser": "Safari", "user_count": 364, "percent_of_users": 0.1587440034888792, "total_visits": 397, "total_hits": 1260, "total_page_views": 1137, "sold_count": 24 }, { "browser": "Firefox", "user_count": 95, "percent_of_users": 0.04143044047099869, "total_visits": 101, "total_hits": 390, "total_page_views": 343, "sold_count": 10 }, { "browser": "Internet Explorer", "user_count": 50, "percent_of_users": 0.021805494984736152, "total_visits": 54, "total_hits": 107, "total_page_views": 102, "sold_count": 1 }, { "browser": "Edge", "user_count": 23, "percent_of_users": 0.01003052769297863, "total_visits": 23, "total_hits": 63, "total_page_views": 55, "sold_count": 0 }, { "browser": "Opera Mini", "user_count": 21, "percent_of_users": 0.009158307893589184, "total_visits": 21, "total_hits": 29, "total_page_views": 28, "sold_count": 0 }, { "browser": "Android Webview", "user_count": 17, "percent_of_users": 0.007413868294810292, "total_visits": 19, "total_hits": 363, "total_page_views": 211, "sold_count": 1 }, { "browser": "Opera", "user_count": 11, "percent_of_users": 0.004797208896641954, "total_visits": 16, "total_hits": 55, "total_page_views": 49, "sold_count": 1 }, { "browser": "Safari (in-app)", "user_count": 8, "percent_of_users": 0.0034888791975577847, "total_visits": 10, "total_hits": 33, "total_page_views": 29, "sold_count": 1 }, { "browser": "UC Browser", "user_count": 6, "percent_of_users": 0.0026166593981683385, "total_visits": 6, "total_hits": 22, "total_page_views": 14, "sold_count": 0 }, { "browser": "Android Browser", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 3, "total_page_views": 3, "sold_count": 0 }, { "browser": "Nokia Browser", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 3, "total_page_views": 3, "sold_count": 0 }, { "browser": "Coc Coc", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 2, "total_page_views": 2, "sold_count": 0 }, { "browser": "YaBrowser", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 6, "total_page_views": 6, "sold_count": 0 }, { "browser": "Mozilla Compatible Agent", "user_count": 1, "percent_of_users": 0.0004361098996947231, "total_visits": 1, "total_hits": 1, "total_page_views": 1, "sold_count": 0 } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN ga_sessions.device."browser" END as "browser__1", CASE WHEN group_set=1 THEN count(distinct ga_sessions."fullVisitorId") END as "user_count__1", (CASE WHEN group_set=1 THEN count(distinct ga_sessions."fullVisitorId") END)*1.0/MAX((CASE WHEN group_set=0 THEN count(distinct ga_sessions."fullVisitorId") END)) OVER () as "percent_of_users__1", CASE WHEN group_set=1 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."visits"})) a ) ),0) END as "total_visits__1", CASE WHEN group_set=1 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."hits"})) a ) ),0) END as "total_hits__1", CASE WHEN group_set=1 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."pageviews"})) a ) ),0) END as "total_page_views__1", CASE WHEN group_set=1 THEN COUNT(DISTINCT CASE WHEN product_0."productQuantity">0 THEN ga_sessions."__distinct_key" || 'x' || hits_0_outer.__row_id END) END as "sold_count__1" FROM (SELECT GEN_RANDOM_UUID() as __distinct_key, x.* FROM '../data/ga_sample.parquet' as x) as ga_sessions LEFT JOIN LATERAL (SELECT UNNEST(GENERATE_SERIES(1, length(ga_sessions."hits"),1)) as __row_id, UNNEST(ga_sessions."hits"), 1 as ignoreme) as hits_0_outer(__row_id, hits_0,ignoreme) ON hits_0_outer.ignoreme=1 LEFT JOIN LATERAL (SELECT UNNEST(hits_0."product"), 1 as ignoreme) as product_0_outer(product_0,ignoreme) ON product_0_outer.ignoreme=1 CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2 ) SELECT "browser__1" as "browser", MAX(CASE WHEN group_set=1 THEN "user_count__1" END) as "user_count", MAX(CASE WHEN group_set=1 THEN "percent_of_users__1" END) as "percent_of_users", MAX(CASE WHEN group_set=1 THEN "total_visits__1" END) as "total_visits", MAX(CASE WHEN group_set=1 THEN "total_hits__1" END) as "total_hits", MAX(CASE WHEN group_set=1 THEN "total_page_views__1" END) as "total_page_views", MAX(CASE WHEN group_set=1 THEN "sold_count__1" END) as "sold_count" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST
With Nested Results
run: ga_sessions -> { group_by: device.browser aggregate: user_count percent_of_users total_visits total_hits total_page_views sold_count nest: by_source is { where: trafficSource.`source` != '(direct)' group_by: trafficSource.`source` aggregate: user_count percent_of_users hits_count total_visits session_count limit: 10 } }
| browser | user_count | percent_of_users | total_visits | total_hits | total_page_views | sold_count | by_source | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Chrome | 1,689 | 73.66% | 1,900 | 10,896 | 8,956 | 642 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Safari | 364 | 15.87% | 397 | 1,260 | 1,137 | 24 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Firefox | 95 | 4.14% | 101 | 390 | 343 | 10 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Internet Explorer | 50 | 2.18% | 54 | 107 | 102 | 1 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Edge | 23 | 1% | 23 | 63 | 55 | 0 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Opera Mini | 21 | 0.92% | 21 | 29 | 28 | 0 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Android Webview | 17 | 0.74% | 19 | 363 | 211 | 1 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Opera | 11 | 0.48% | 16 | 55 | 49 | 1 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Safari (in-app) | 8 | 0.35% | 10 | 33 | 29 | 1 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| UC Browser | 6 | 0.26% | 6 | 22 | 14 | 0 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Coc Coc | 2 | 0.09% | 2 | 2 | 2 | 0 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Nokia Browser | 2 | 0.09% | 2 | 3 | 3 | 0 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| YaBrowser | 2 | 0.09% | 2 | 6 | 6 | 0 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Android Browser | 2 | 0.09% | 2 | 3 | 3 | 0 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Mozilla Compatible Agent | 1 | 0.04% | 1 | 1 | 1 | 0 |
|
[ { "browser": "Chrome", "user_count": 1689, "percent_of_users": 0.7365896205843873, "total_visits": 1900, "total_hits": 10896, "total_page_views": 8956, "sold_count": 642, "by_source": [ { "source": "youtube.com", "user_count": 104, "percent_of_users": 0.0615748963883955, "hits_count": 209, "total_visits": 105, "session_count": 105 }, { "source": "analytics.google.com", "user_count": 46, "percent_of_users": 0.027235050325636473, "hits_count": 96, "total_visits": 50, "session_count": 50 }, { "source": "Partners", "user_count": 36, "percent_of_users": 0.021314387211367674, "hits_count": 102, "total_visits": 39, "session_count": 39 }, { "source": "google.com", "user_count": 9, "percent_of_users": 0.0053285968028419185, "hits_count": 15, "total_visits": 10, "session_count": 10 }, { "source": "sites.google.com", "user_count": 8, "percent_of_users": 0.004736530491415038, "hits_count": 34, "total_visits": 8, "session_count": 8 }, { "source": "dfa", "user_count": 5, "percent_of_users": 0.002960331557134399, "hits_count": 55, "total_visits": 10, "session_count": 10 }, { "source": "quora.com", "user_count": 5, "percent_of_users": 0.002960331557134399, "hits_count": 12, "total_visits": 6, "session_count": 6 }, { "source": "groups.google.com", "user_count": 4, "percent_of_users": 0.002368265245707519, "hits_count": 9, "total_visits": 4, "session_count": 4 }, { "source": "facebook.com", "user_count": 4, "percent_of_users": 0.002368265245707519, "hits_count": 15, "total_visits": 4, "session_count": 4 }, { "source": "baidu", "user_count": 4, "percent_of_users": 0.002368265245707519, "hits_count": 8, "total_visits": 4, "session_count": 4 } ] }, { "browser": "Safari", "user_count": 364, "percent_of_users": 0.1587440034888792, "total_visits": 397, "total_hits": 1260, "total_page_views": 1137, "sold_count": 24, "by_source": [ { "source": "youtube.com", "user_count": 26, "percent_of_users": 0.07142857142857142, "hits_count": 33, "total_visits": 26, "session_count": 26 }, { "source": "dfa", "user_count": 2, "percent_of_users": 0.005494505494505495, "hits_count": 3, "total_visits": 3, "session_count": 3 }, { "source": "support.google.com", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 1, "total_visits": 1, "session_count": 1 }, { "source": "qiita.com", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 1, "total_visits": 1, "session_count": 1 }, { "source": "yahoo", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 2, "total_visits": 1, "session_count": 1 }, { "source": "baidu", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 2, "total_visits": 1, "session_count": 1 }, { "source": "m.facebook.com", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 1, "total_visits": 1, "session_count": 1 }, { "source": "Partners", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 1, "total_visits": 1, "session_count": 1 }, { "source": "facebook.com", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 14, "total_visits": 1, "session_count": 1 }, { "source": "lm.facebook.com", "user_count": 1, "percent_of_users": 0.0027472527472527475, "hits_count": 2, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Firefox", "user_count": 95, "percent_of_users": 0.04143044047099869, "total_visits": 101, "total_hits": 390, "total_page_views": 343, "sold_count": 10, "by_source": [ { "source": "youtube.com", "user_count": 15, "percent_of_users": 0.15789473684210525, "hits_count": 19, "total_visits": 15, "session_count": 15 }, { "source": "analytics.google.com", "user_count": 6, "percent_of_users": 0.06315789473684211, "hits_count": 15, "total_visits": 6, "session_count": 6 }, { "source": "Partners", "user_count": 3, "percent_of_users": 0.031578947368421054, "hits_count": 6, "total_visits": 3, "session_count": 3 }, { "source": "reddit.com", "user_count": 2, "percent_of_users": 0.021052631578947368, "hits_count": 6, "total_visits": 2, "session_count": 2 }, { "source": "blog.golang.org", "user_count": 2, "percent_of_users": 0.021052631578947368, "hits_count": 3, "total_visits": 2, "session_count": 2 }, { "source": "int.search.tb.ask.com", "user_count": 1, "percent_of_users": 0.010526315789473684, "hits_count": 3, "total_visits": 1, "session_count": 1 }, { "source": "qiita.com", "user_count": 1, "percent_of_users": 0.010526315789473684, "hits_count": 15, "total_visits": 1, "session_count": 1 }, { "source": "dfa", "user_count": 1, "percent_of_users": 0.010526315789473684, "hits_count": 6, "total_visits": 2, "session_count": 2 }, { "source": "l.facebook.com", "user_count": 1, "percent_of_users": 0.010526315789473684, "hits_count": 1, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Internet Explorer", "user_count": 50, "percent_of_users": 0.021805494984736152, "total_visits": 54, "total_hits": 107, "total_page_views": 102, "sold_count": 1, "by_source": [ { "source": "youtube.com", "user_count": 13, "percent_of_users": 0.26, "hits_count": 15, "total_visits": 13, "session_count": 13 }, { "source": "Partners", "user_count": 3, "percent_of_users": 0.06, "hits_count": 3, "total_visits": 3, "session_count": 3 }, { "source": "facebook.com", "user_count": 1, "percent_of_users": 0.02, "hits_count": 6, "total_visits": 1, "session_count": 1 }, { "source": "qiita.com", "user_count": 1, "percent_of_users": 0.02, "hits_count": 2, "total_visits": 1, "session_count": 1 }, { "source": "yahoo", "user_count": 1, "percent_of_users": 0.02, "hits_count": 3, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Edge", "user_count": 23, "percent_of_users": 0.01003052769297863, "total_visits": 23, "total_hits": 63, "total_page_views": 55, "sold_count": 0, "by_source": [ { "source": "youtube.com", "user_count": 10, "percent_of_users": 0.43478260869565216, "hits_count": 39, "total_visits": 10, "session_count": 10 }, { "source": "Partners", "user_count": 2, "percent_of_users": 0.08695652173913043, "hits_count": 3, "total_visits": 2, "session_count": 2 }, { "source": "bing", "user_count": 1, "percent_of_users": 0.043478260869565216, "hits_count": 4, "total_visits": 1, "session_count": 1 }, { "source": "l.facebook.com", "user_count": 1, "percent_of_users": 0.043478260869565216, "hits_count": 1, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Opera Mini", "user_count": 21, "percent_of_users": 0.009158307893589184, "total_visits": 21, "total_hits": 29, "total_page_views": 28, "sold_count": 0, "by_source": [ { "source": "youtube.com", "user_count": 3, "percent_of_users": 0.14285714285714285, "hits_count": 3, "total_visits": 3, "session_count": 3 } ] }, { "browser": "Android Webview", "user_count": 17, "percent_of_users": 0.007413868294810292, "total_visits": 19, "total_hits": 363, "total_page_views": 211, "sold_count": 1, "by_source": [ { "source": "Partners", "user_count": 4, "percent_of_users": 0.23529411764705882, "hits_count": 34, "total_visits": 4, "session_count": 4 }, { "source": "youtube.com", "user_count": 3, "percent_of_users": 0.17647058823529413, "hits_count": 4, "total_visits": 4, "session_count": 4 }, { "source": "m.facebook.com", "user_count": 1, "percent_of_users": 0.058823529411764705, "hits_count": 3, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Opera", "user_count": 11, "percent_of_users": 0.004797208896641954, "total_visits": 16, "total_hits": 55, "total_page_views": 49, "sold_count": 1, "by_source": [ { "source": "facebook.com", "user_count": 1, "percent_of_users": 0.09090909090909091, "hits_count": 1, "total_visits": 1, "session_count": 1 }, { "source": "google.com", "user_count": 1, "percent_of_users": 0.09090909090909091, "hits_count": 19, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Safari (in-app)", "user_count": 8, "percent_of_users": 0.0034888791975577847, "total_visits": 10, "total_hits": 33, "total_page_views": 29, "sold_count": 1, "by_source": [ { "source": "lm.facebook.com", "user_count": 1, "percent_of_users": 0.125, "hits_count": 2, "total_visits": 1, "session_count": 1 }, { "source": "m.facebook.com", "user_count": 1, "percent_of_users": 0.125, "hits_count": 4, "total_visits": 3, "session_count": 3 }, { "source": "pinterest.com", "user_count": 1, "percent_of_users": 0.125, "hits_count": 11, "total_visits": 1, "session_count": 1 } ] }, { "browser": "UC Browser", "user_count": 6, "percent_of_users": 0.0026166593981683385, "total_visits": 6, "total_hits": 22, "total_page_views": 14, "sold_count": 0, "by_source": [ { "source": "youtube.com", "user_count": 1, "percent_of_users": 0.16666666666666666, "hits_count": 1, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Coc Coc", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 2, "total_page_views": 2, "sold_count": 0, "by_source": [ { "source": "youtube.com", "user_count": 1, "percent_of_users": 0.5, "hits_count": 1, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Nokia Browser", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 3, "total_page_views": 3, "sold_count": 0, "by_source": [ { "source": "google.com", "user_count": 1, "percent_of_users": 0.5, "hits_count": 1, "total_visits": 1, "session_count": 1 } ] }, { "browser": "YaBrowser", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 6, "total_page_views": 6, "sold_count": 0, "by_source": [ { "source": "analytics.google.com", "user_count": 1, "percent_of_users": 0.5, "hits_count": 3, "total_visits": 1, "session_count": 1 }, { "source": "youtube.com", "user_count": 1, "percent_of_users": 0.5, "hits_count": 3, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Android Browser", "user_count": 2, "percent_of_users": 0.0008722197993894462, "total_visits": 2, "total_hits": 3, "total_page_views": 3, "sold_count": 0, "by_source": [ { "source": "m.baidu.com", "user_count": 1, "percent_of_users": 0.5, "hits_count": 1, "total_visits": 1, "session_count": 1 } ] }, { "browser": "Mozilla Compatible Agent", "user_count": 1, "percent_of_users": 0.0004361098996947231, "total_visits": 1, "total_hits": 1, "total_page_views": 1, "sold_count": 0, "by_source": [ { "source": "youtube.com", "user_count": 1, "percent_of_users": 1, "hits_count": 1, "total_visits": 1, "session_count": 1 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set IN (1,2) THEN ga_sessions.device."browser" END as "browser__1", CASE WHEN group_set=1 THEN count(distinct ga_sessions."fullVisitorId") END as "user_count__1", (CASE WHEN group_set=1 THEN count(distinct ga_sessions."fullVisitorId") END)*1.0/MAX((CASE WHEN group_set=0 THEN count(distinct ga_sessions."fullVisitorId") END)) OVER () as "percent_of_users__1", CASE WHEN group_set=1 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."visits"})) a ) ),0) END as "total_visits__1", CASE WHEN group_set=1 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."hits"})) a ) ),0) END as "total_hits__1", CASE WHEN group_set=1 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."pageviews"})) a ) ),0) END as "total_page_views__1", CASE WHEN group_set=1 THEN COUNT(DISTINCT CASE WHEN product_0."productQuantity">0 THEN ga_sessions."__distinct_key" || 'x' || hits_0_outer.__row_id END) END as "sold_count__1", CASE WHEN group_set=2 THEN ga_sessions.trafficSource."source" END as "source__2", CASE WHEN group_set=2 THEN count(distinct ga_sessions."fullVisitorId") END as "user_count__2", (CASE WHEN group_set=2 THEN count(distinct ga_sessions."fullVisitorId") END)*1.0/MAX((CASE WHEN group_set=1 THEN count(distinct ga_sessions."fullVisitorId") END)) OVER (PARTITION BY CASE WHEN group_set IN (1,2) THEN ga_sessions.device."browser" END) as "percent_of_users__2", CASE WHEN group_set=2 THEN COUNT(DISTINCT ga_sessions."__distinct_key" || 'x' || hits_0_outer.__row_id) END as "hits_count__2", CASE WHEN group_set=2 THEN COALESCE(( SELECT SUM(a.val) as value FROM ( SELECT UNNEST(list(distinct {key:ga_sessions."__distinct_key", val: ga_sessions.totals."visits"})) a ) ),0) END as "total_visits__2", CASE WHEN group_set=2 THEN COUNT(DISTINCT ga_sessions."__distinct_key") END as "session_count__2" FROM (SELECT GEN_RANDOM_UUID() as __distinct_key, x.* FROM '../data/ga_sample.parquet' as x) as ga_sessions LEFT JOIN LATERAL (SELECT UNNEST(GENERATE_SERIES(1, length(ga_sessions."hits"),1)) as __row_id, UNNEST(ga_sessions."hits"), 1 as ignoreme) as hits_0_outer(__row_id, hits_0,ignoreme) ON hits_0_outer.ignoreme=1 LEFT JOIN LATERAL (SELECT UNNEST(hits_0."product"), 1 as ignoreme) as product_0_outer(product_0,ignoreme) ON product_0_outer.ignoreme=1 CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set ) as group_set WHERE (group_set NOT IN (2) OR (group_set IN (2) AND COALESCE(NOT(ga_sessions.trafficSource."source"='(direct)'),FALSE))) GROUP BY 1,2,9 ) SELECT "browser__1" as "browser", MAX(CASE WHEN group_set=1 THEN "user_count__1" END) as "user_count", MAX(CASE WHEN group_set=1 THEN "percent_of_users__1" END) as "percent_of_users", MAX(CASE WHEN group_set=1 THEN "total_visits__1" END) as "total_visits", MAX(CASE WHEN group_set=1 THEN "total_hits__1" END) as "total_hits", MAX(CASE WHEN group_set=1 THEN "total_page_views__1" END) as "total_page_views", MAX(CASE WHEN group_set=1 THEN "sold_count__1" END) as "sold_count", COALESCE(LIST({ "source": "source__2", "user_count": "user_count__2", "percent_of_users": "percent_of_users__2", "hits_count": "hits_count__2", "total_visits": "total_visits__2", "session_count": "session_count__2"} ORDER BY "user_count__2" desc NULLS LAST) FILTER (WHERE group_set=2)[1:10],[]) as "by_source" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST