Count on one custom dimension and group by another with BigQuery

Question

I'm totally new to BigQuery, please excuse me for any obvious mistake. I'm trying to build a query where I can count the number of distinct element from one custom dimension and group this by another custom dimension.

I tried this but It doesn't work :

SELECT
  MAX(IF(hits.customDimensions.index=7,hits.customDimensions.value,NULL)) AS Author,
  COUNT(MAX(IF(hits.customDimensions.index=10,hits.customDimensions.value,NULL))) AS Articles
FROM (
  SELECT
    *
  FROM
    TABLE_DATE_RANGE([blablabla-blabla-115411:104672022.ga_sessions_test], TIMESTAMP('20160927'), TIMESTAMP('20161024'))) AS t0
GROUP BY
MAX(IF(hits.customDimensions.index=7,hits.customDimensions.value,NULL)) AS Author,

Answer 1

Using standard SQL (uncheck "Use Legacy SQL" under "Show Options"), does this query work? For each entry in hits, it selects the value for an index of 7 as the author, and then counts the number of entries where index is 10 as the number of articles. It makes the assumption that there is at most one entry with an index of 7 in customDimensions.

SELECT
  (SELECT value FROM UNNEST(hits.customDimensions)
   WHERE index = 7) AS Author,
  SUM((SELECT COUNT(*) FROM UNNEST(hits.customDimensions)
       WHERE index = 10)) AS Articles
FROM
  `your-dataset.ga_sessions_test` AS t, UNNEST(t.hits) AS hits
WHERE _PARTITIONTIME BETWEEN '2016-09-27' AND '2016-10-24'
GROUP BY Author;