Malloy Documentation
search

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:

document
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

document
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
}
QUERY RESULTS
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com17848.5%328180180
analytics.google.com5314.44%1145757
Partners4913.35%1495252
google.com113%351212
sites.google.com82.18%3488
dfa82.18%641515
facebook.com71.91%3677
quora.com51.36%1266
baidu51.36%1055
groups.google.com41.09%944
[
  {
    "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": "sites.google.com",
    "user_count": 8,
    "percent_of_users": 0.021798365122615803,
    "hits_count": 34,
    "total_visits": 8,
    "session_count": 8
  },
  {
    "source": "dfa",
    "user_count": 8,
    "percent_of_users": 0.021798365122615803,
    "hits_count": 64,
    "total_visits": 15,
    "session_count": 15
  },
  {
    "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": "groups.google.com",
    "user_count": 4,
    "percent_of_users": 0.010899182561307902,
    "hits_count": 9,
    "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

document
run: ga_sessions -> {
  group_by: device.browser
  aggregate:
    user_count
    percent_of_users
    total_visits
    total_hits
    total_page_views
    sold_count
}
QUERY RESULTS
browseruser_​countpercent_​of_​userstotal_​visitstotal_​hitstotal_​page_​viewssold_​count
Chrome1,68973.66%1,90010,8968,956642
Safari36415.87%3971,2601,13724
Firefox954.14%10139034310
Internet Explorer502.18%541071021
Edge231%2363550
Opera Mini210.92%2129280
Android Webview170.74%193632111
Opera110.48%1655491
Safari (in-app)80.35%1033291
UC Browser60.26%622140
YaBrowser20.09%2660
Nokia Browser20.09%2330
Coc Coc20.09%2220
Android Browser20.09%2330
Mozilla Compatible Agent10.04%1110
[
  {
    "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": "YaBrowser",
    "user_count": 2,
    "percent_of_users": 0.0008722197993894462,
    "total_visits": 2,
    "total_hits": 6,
    "total_page_views": 6,
    "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": "Android Browser",
    "user_count": 2,
    "percent_of_users": 0.0008722197993894462,
    "total_visits": 2,
    "total_hits": 3,
    "total_page_views": 3,
    "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

document
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
  }
}
QUERY RESULTS
browseruser_​countpercent_​of_​userstotal_​visitstotal_​hitstotal_​page_​viewssold_​countby_​source
Chrome1,68973.66%1,90010,8968,956642
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com1046.16%209105105
analytics.google.com462.72%965050
Partners362.13%1023939
google.com90.53%151010
sites.google.com80.47%3488
quora.com50.3%1266
dfa50.3%551010
groups.google.com40.24%944
facebook.com40.24%1544
baidu40.24%844
Safari36415.87%3971,2601,13724
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com267.14%332626
dfa20.55%333
lm.facebook.com10.27%211
yahoo10.27%211
qiita.com10.27%111
baidu10.27%211
Partners10.27%111
support.google.com10.27%111
m.facebook.com10.27%111
facebook.com10.27%1411
Firefox954.14%10139034310
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com1515.79%191515
analytics.google.com66.32%1566
Partners33.16%633
reddit.com22.11%622
blog.golang.org22.11%322
int.search.tb.ask.com11.05%311
dfa11.05%622
qiita.com11.05%1511
l.facebook.com11.05%111
Internet Explorer502.18%541071021
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com1326%151313
Partners36%333
facebook.com12%611
yahoo12%311
qiita.com12%211
Edge231%2363550
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com1043.48%391010
Partners28.7%322
l.facebook.com14.35%111
bing14.35%411
Opera Mini210.92%2129280
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com314.29%333
Android Webview170.74%193632111
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
Partners423.53%3444
youtube.com317.65%444
m.facebook.com15.88%311
Opera110.48%1655491
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
facebook.com19.09%111
google.com19.09%1911
Safari (in-app)80.35%1033291
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
lm.facebook.com112.5%211
m.facebook.com112.5%433
pinterest.com112.5%1111
UC Browser60.26%622140
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com116.67%111
YaBrowser20.09%2660
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com150%311
analytics.google.com150%311
Coc Coc20.09%2220
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com150%111
Nokia Browser20.09%2330
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
google.com150%111
Android Browser20.09%2330
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
m.baidu.com150%111
Mozilla Compatible Agent10.04%1110
sourceuser_​countpercent_​of_​usershits_​counttotal_​visitssession_​count
youtube.com1100%111
[
  {
    "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": "quora.com",
        "user_count": 5,
        "percent_of_users": 0.002960331557134399,
        "hits_count": 12,
        "total_visits": 6,
        "session_count": 6
      },
      {
        "source": "dfa",
        "user_count": 5,
        "percent_of_users": 0.002960331557134399,
        "hits_count": 55,
        "total_visits": 10,
        "session_count": 10
      },
      {
        "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": "lm.facebook.com",
        "user_count": 1,
        "percent_of_users": 0.0027472527472527475,
        "hits_count": 2,
        "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": "qiita.com",
        "user_count": 1,
        "percent_of_users": 0.0027472527472527475,
        "hits_count": 1,
        "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": "Partners",
        "user_count": 1,
        "percent_of_users": 0.0027472527472527475,
        "hits_count": 1,
        "total_visits": 1,
        "session_count": 1
      },
      {
        "source": "support.google.com",
        "user_count": 1,
        "percent_of_users": 0.0027472527472527475,
        "hits_count": 1,
        "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": "facebook.com",
        "user_count": 1,
        "percent_of_users": 0.0027472527472527475,
        "hits_count": 14,
        "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": "dfa",
        "user_count": 1,
        "percent_of_users": 0.010526315789473684,
        "hits_count": 6,
        "total_visits": 2,
        "session_count": 2
      },
      {
        "source": "qiita.com",
        "user_count": 1,
        "percent_of_users": 0.010526315789473684,
        "hits_count": 15,
        "total_visits": 1,
        "session_count": 1
      },
      {
        "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": "yahoo",
        "user_count": 1,
        "percent_of_users": 0.02,
        "hits_count": 3,
        "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
      }
    ]
  },
  {
    "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": "l.facebook.com",
        "user_count": 1,
        "percent_of_users": 0.043478260869565216,
        "hits_count": 1,
        "total_visits": 1,
        "session_count": 1
      },
      {
        "source": "bing",
        "user_count": 1,
        "percent_of_users": 0.043478260869565216,
        "hits_count": 4,
        "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": "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": "youtube.com",
        "user_count": 1,
        "percent_of_users": 0.5,
        "hits_count": 3,
        "total_visits": 1,
        "session_count": 1
      },
      {
        "source": "analytics.google.com",
        "user_count": 1,
        "percent_of_users": 0.5,
        "hits_count": 3,
        "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": "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