Osiyuks

WITH students AS
(
  SELECT 9 AS age UNION ALL
  SELECT 20 AS age UNION ALL
  SELECT 25 AS age UNION ALL
  SELECT 31 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1

Osiyuks

SELECT 
 ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'][ORDINAL(EXTRACT(DAYOFWEEK FROM date(TIMESTAMP_MICROS(event_timestamp))))] as dayofweek,
 FORMAT_DATETIME('%H', datetime(TIMESTAMP_MICROS(event_timestamp))) as hour,
 SUM(IF(event_name = 'use_extra_steps', 1, 0)) as use_extra_steps
FROM `firebase-public-project.analytics_153293282.events_2018*`
 GROUP BY dayofweek, hour
 ORDER BY dayofweek, hour

Osiyuks

SELECT
  * EXCEPT (DATE, Country)
FROM
  `<project>.<dataset>.<table_name>`

Osiyuks

SELECT 
  COUNT(*)
FROM `<project>.<dataset>.<table_name>`

FOR SYSTEM TIME AS OF
  TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 day)

Osiyuks

WITH countries AS (
  SELECT * FROM UNNEST(['ru','ua','by','us','fr','jp']) country
)

SELECT *
  , (SELECT emoji FROM `fh-bigquery.util.country_emoji_flags` WHERE country=iso_lower) emoji
  , (SELECT name FROM `fh-bigquery.util.country_emoji_flags` WHERE country=iso_lower) country_name
FROM countries

Osiyuks

SELECT
  event_name,
  ARRAY_AGG(STRUCT(name, format_value, example_value, varieties_of_value)) AS property FROM(
  SELECT
    event_name,
    name,
    format_value,
    ANY_VALUE(example_value) AS example_value,
    COUNT(DISTINCT example_value) AS varieties_of_value
  FROM (

Osiyuks

WITH source_of_ip_addresses AS (
  SELECT REGEXP_REPLACE(contributor_ip, 'xxx', '0')  ip, COUNT(*) c
  FROM `publicdata.samples.wikipedia`
  WHERE contributor_ip IS NOT null  
  GROUP BY 1
)

SELECT country_name, SUM(c) c, ST_GeogPoint(AVG(longitude), AVG(latitude)) point
FROM (
  SELECT ip, continent_name, country_name, c, latitude, longitude, geoname_id

Osiyuks

with t1 as (

SELECT  *,
lag(timestamp) over(partition by user_id order by timestamp) as timestamp_lag,
if(TIMESTAMP_DIFF(timestamp, lag(timestamp) over(partition by user_id order by timestamp),minute)<30,0,1) as session_start
FROM `table_name` 

), t2 as (

select *,

Osiyuks

with t1 as (

SELECT  *,
lag(timestamp) over(partition by user_id order by timestamp) as timestamp_lag,
if(TIMESTAMP_DIFF(timestamp, lag(timestamp) over(partition by user_id order by timestamp),minute)<30,0,1) as session_start
FROM `table_name` 

)

select *,

Osiyuks

SELECT  *,
lag(timestamp) over(partition by user_id order by timestamp) as timestamp_lag,
if(TIMESTAMP_DIFF(timestamp, lag(timestamp) over(partition by user_id order by timestamp),minute)<30,0,1) as session_start
FROM `table_name`