SELECTdevice.browser,SUM ( totals.transactions ) AS total_transactionsFROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`WHERE_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'GROUP BY device.browserHAVING SUM ( totals.transactions ) > 0 ORDER BYtotal_transactions DESC

Using STRING_AGG(CONCAT(colname, ''))

with check_countries as (SELECT count(distinct country_region)FROM `bigquery-public-data.covid19_jhu_csse.summary` LIMIT 1000),Hu_filter as (select 'HU' as country, * from `bigquery-public-data.covid19_jhu_csse.summary`where upper(country_region) = 'HUNGARY'),filtered as (select date, confirmed,…

with check_countries as (SELECT count(distinct country_region)FROM `bigquery-public-data.covid19_jhu_csse.summary` LIMIT 1000),Ro_filter as (select 'HU' as country, * from `bigquery-public-data.covid19_jhu_csse.summary`where upper(country_region) = 'ROMANIA'),filtered as (select date, confirmed,…

 with prep as (SELECT *, LAG(confirmed_cases, 1)OVER (PARTITION BY state_name ORDER BY date ASC) AS prev_dayFROM `bigquery-public-data.covid19_nyt.us_states`where upper(state_name) = 'TEXAS'and date >= '2021-03-01'order by date asc) select date, confirmed_cases - prev_day as new_cases --since…

with prep as (SELECT * except (description, image_thumbnail) FROM `properati-data-public.properties_ar.properties_sell_201511` where price is not null and price != 0), average_usd as (select concat(extract(year from created_on),'-',extract(month from created_on)) as yearmonth,round(avg(price),0) as…

Where is Texas in the number of states compared to the other states?Pretty high. SELECTstate_name, confirmed_cases, deaths, round(deaths/confirmed_cases,4) as death_rateFROM `bigquery-public-data.covid19_nyt.us_states` where date in (select max(date) from…

Just because I can.   Let's add something else too:

I love it, when I want to chronologically sort year & months formatted like 2016-1, 2016-2..2016-10 and the result is like: 2016-1, 2016-10, 2016-2. This needs to stop. with prep as (SELECT * ,cast(concat(cast(year as string), '-', case when length(cast(month as string)) = 1 then…

The table: Let's start with most popular name throughout all the years: SELECT A.*, B.* FROM `bigquery-public-data.usa_names.usa_1910_2013` A inner join (select name as name2, sum(number) as res from `bigquery-public-data.usa_names.usa_1910_2013` group by name order by sum(number) desc) B on…

Situation: You have a table with the ID of the customer and the name. In another table you have the ID of the customers and their offers. You want to see the number of offers each client has got. And by client, you want the names, not the ID. Example tables could look like this: with table1 as…

So one day I woke up and I was like: So I booted up my free account and started typing: with test as (select 'ABCD1234' as field) select regexp_extract(field, '[0-9]{1,3}') as extracted from test --retunrs 123union allselect regexp_extract(field, '[0-9]{1,4}') as extracted from test --…

The situation: let's say I want to know the number of cases in Italy by province first. The dataset: Warm-Up queries: Now, to get the same result with Analytic function, displaying the total sum of the region in every row.  Okay, but this is not so much fun when you have only a few…