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
[
  {
    "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
      base.trafficSource."source"
      END as "source__1",
    CASE WHEN group_set=1 THEN
      count(distinct base."fullVisitorId")
      END as "user_count__1",
    (CASE WHEN group_set=1 THEN
      count(distinct base."fullVisitorId")
      END)*1.0/MAX((CASE WHEN group_set=0 THEN
      count(distinct base."fullVisitorId")
      END)) OVER () as "percent_of_users__1",
    CASE WHEN group_set=1 THEN
      COUNT(DISTINCT base."__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:base."__distinct_key", val: base.totals."visits"})) a
          )
        ),0)
      END as "total_visits__1",
    CASE WHEN group_set=1 THEN
      COUNT(DISTINCT base."__distinct_key")
      END as "session_count__1"
  FROM (SELECT GEN_RANDOM_UUID() as "__distinct_key", x.*  FROM '../data/ga_sample.parquet' as x) as base
  LEFT JOIN LATERAL (SELECT UNNEST(GENERATE_SERIES(1, length(base."hits"),1)) as __row_id, UNNEST(base."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(base.trafficSource."source"!='(direct)',true)
  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
[
  {
    "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": "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": "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
      base.device."browser"
      END as "browser__1",
    CASE WHEN group_set=1 THEN
      count(distinct base."fullVisitorId")
      END as "user_count__1",
    (CASE WHEN group_set=1 THEN
      count(distinct base."fullVisitorId")
      END)*1.0/MAX((CASE WHEN group_set=0 THEN
      count(distinct base."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:base."__distinct_key", val: base.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:base."__distinct_key", val: base.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:base."__distinct_key", val: base.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 base."__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 base
  LEFT JOIN LATERAL (SELECT UNNEST(GENERATE_SERIES(1, length(base."hits"),1)) as __row_id, UNNEST(base."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
[
  {
    "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": "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
      },
      {
        "source": "groups.google.com",
        "user_count": 4,
        "percent_of_users": 0.002368265245707519,
        "hits_count": 9,
        "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": "baidu",
        "user_count": 1,
        "percent_of_users": 0.0027472527472527475,
        "hits_count": 2,
        "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": "qiita.com",
        "user_count": 1,
        "percent_of_users": 0.0027472527472527475,
        "hits_count": 1,
        "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
      },
      {
        "source": "support.google.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": "Partners",
        "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
      }
    ]
  },
  {
    "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": "blog.golang.org",
        "user_count": 2,
        "percent_of_users": 0.021052631578947368,
        "hits_count": 3,
        "total_visits": 2,
        "session_count": 2
      },
      {
        "source": "reddit.com",
        "user_count": 2,
        "percent_of_users": 0.021052631578947368,
        "hits_count": 6,
        "total_visits": 2,
        "session_count": 2
      },
      {
        "source": "dfa",
        "user_count": 1,
        "percent_of_users": 0.010526315789473684,
        "hits_count": 6,
        "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": "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": "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
      base.device."browser"
      END as "browser__1",
    CASE WHEN group_set=1 THEN
      count(distinct base."fullVisitorId")
      END as "user_count__1",
    (CASE WHEN group_set=1 THEN
      count(distinct base."fullVisitorId")
      END)*1.0/MAX((CASE WHEN group_set=0 THEN
      count(distinct base."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:base."__distinct_key", val: base.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:base."__distinct_key", val: base.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:base."__distinct_key", val: base.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 base."__distinct_key" || 'x' || hits_0_outer.__row_id END)
      END as "sold_count__1",
    CASE WHEN group_set=2 THEN
      base.trafficSource."source"
      END as "source__2",
    CASE WHEN group_set=2 THEN
      count(distinct base."fullVisitorId")
      END as "user_count__2",
    (CASE WHEN group_set=2 THEN
      count(distinct base."fullVisitorId")
      END)*1.0/MAX((CASE WHEN group_set=1 THEN
      count(distinct base."fullVisitorId")
      END)) OVER (PARTITION BY CASE WHEN group_set IN (1,2) THEN
      base.device."browser"
      END) as "percent_of_users__2",
    CASE WHEN group_set=2 THEN
      COUNT(DISTINCT base."__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:base."__distinct_key", val: base.totals."visits"})) a
          )
        ),0)
      END as "total_visits__2",
    CASE WHEN group_set=2 THEN
      COUNT(DISTINCT base."__distinct_key")
      END as "session_count__2"
  FROM (SELECT GEN_RANDOM_UUID() as "__distinct_key", x.*  FROM '../data/ga_sample.parquet' as x) as base
  LEFT JOIN LATERAL (SELECT UNNEST(GENERATE_SERIES(1, length(base."hits"),1)) as __row_id, UNNEST(base."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(base.trafficSource."source"!='(direct)',true)))
  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