SELECT
COUNT(SOURCE) AS SOURCE
FROM
`business-metric.project.new_table1`
WHERE
SOURCE = 'google'
ИЛИ
SELECT
COUNT(*) AS SOURCE
FROM
`business-metric.project.new_table1`
WHERE
SOURCE = 'google'
SELECT
COUNT(*) AS count_table
FROM
`business-metric.project.new_table1`
SELECT
COUNT(DISTINCT(source)) AS c_source
FROM
`business-metric.project.new_table1`
SELECT
COUNTIF(event_name = 'page_view') AS count_page_view,
COUNTIF(event_name IN ('in_app_purchase','purchase')) AS count_purchase
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20201201'
AND '20201202'
SELECT
AVG(conversions) AS avg_conv
FROM
`business-metric.project.new_table1`
SELECT
AVG(distinct(conversions)) AS avg_conv
FROM
`business-metric.project.new_table1`
SELECT
MIN(conversions) AS min_conv
FROM
`business-metric.project.new_table1`
SELECT
MAX(conversions) AS max_conv
FROM
`business-metric.project.new_table1`
SELECT
SUM(conversions) AS sum_conv
FROM
`business-metric.project.new_table1`
SELECT
SUM(DISTINCT(conversions)) AS sum_conv
FROM
`business-metric.project.new_table1`
SELECT num,
RANK() OVER (ORDER BY num ASC) AS rank
FROM Numbers
SELECT name,
purchase_time,
source,
RANK() OVER (PARTITION BY source ORDER BY purchase_time ASC) AS ranks
FROM first_visitor
SELECT num,
DENSE_RANK() OVER (ORDER BY num ASC) AS dense_rank
FROM Numbers
SELECT num,
ROW_NUMBER() OVER (ORDER BY num) AS row_num
FROM Numbers
select date,source,conversion,
CUME_DIST() OVER(PARTITION BY date ORDER BY conversion) AS cume_dist
from `united-strategy-279207.Example.conversion`
SELECT name,
purchase_time,
source,
CUME_DIST() OVER (PARTITION BY source ORDER BY purchase_time ASC) AS ranks
FROM first_visitor
select date,source,conversion,
PERCENT_RANK() OVER(PARTITION BY date ORDER BY conversion) AS percent_rank
from `united-strategy-279207.Example.conversion`
SELECT name,
purchase_time,
source,
PERCENT_RANK() OVER (PARTITION BY source ORDER BY purchase_time ASC) AS p_rank
FROM first_visitor
SELECT date, source, conversion,
NTILE(4) OVER(PARTITION BY date ORDER BY conversion) AS n_tile
FROM
`united-strategy-279207.Example.conversion`
SELECT
name,ROUND(coef,1) as r_coef
FROM
`business-metric.project.new_table`
SELECT
ROUND(AVG(conversions),1) AS avg_conv
FROM
`business-metric.project.new_table1`
SELECT
ROUND(AVG(DISTINCT(conversions)),2) as r_avg_conv
FROM
`business-metric.project.new_table1`
select date, source,conversion,
FIRST_VALUE(conversion) OVER(PARTITION BY date ORDER BY date) AS f_value,
LAST_VALUE(conversion) OVER(PARTITION BY date ORDER BY date) AS l_value
from `united-strategy-279207.Example.conversion`
select date, source,conversion,
LAG(conversion) OVER(PARTITION BY date ORDER BY date) AS lag
from `united-strategy-279207.Example.conversion`
select date, source,conversion,
LEAD(conversion) OVER(PARTITION BY date ORDER BY date) AS lead
from `united-strategy-279207.Example.conversion`
select date,source,conversion,
PERCENTILE_CONT(conversion, 0.5) OVER (PARTITION BY date) as p_cont,
PERCENTILE_DISC(conversion, 0.5) OVER(PARTITION BY date) AS p_disc
from `united-strategy-279207.Example.conversion`
SELECT
UPPER(SOURCE) AS u_source
FROM
`business-metric.project.new_table1`
SELECT
LOWER(SOURCE) AS low_source
FROM
`business-metric.project.new_table1`
SELECT
source,LENGTH(SOURCE) AS l_source
FROM
`business-metric.project.new_table1`
SELECT CONCAT('google',' / ', 'cpc') as source_medium
SELECT
CONCAT(source, ' / ', medium) AS source_medium
FROM source_m
SELECT
REPLACE(source_medium,'social','cpm') as new_source
FROM source_m
SELECT
FORMAT("%'d", 123456789) AS f_1,
FORMAT("%'x", 123456789) AS f_2,
FORMAT("%'o", 33333) AS f_3
SELECT
CURRENT_DATE() AS now_d,
CURRENT_DATETIME() AS now_dt,
CURRENT_TIME() AS now_t,
CURRENT_TIMESTAMP() AS c_timestamp
SELECT
-- DATE
DATE(2022, 08, 30) AS date_,
DATE(DATETIME "2022-08-30 23:59:59") AS date_dt,
-- DATETIME
DATETIME(2022, 08, 30, 12, 33, 11) AS datetime,
DATETIME(TIMESTAMP "2022-08-30 11:30:00+00", "Asia/Dubai") AS datetime_tz,
-- TIME
TIME(12, 45, 10) AS time_,
-- TIMESTAMP
TIMESTAMP("2022-08-30 10:25:15+00") AS timestamp_
SELECT EXTRACT(DAY FROM DATE '2022-08-30') AS day
SELECT EXTRACT(DAYOFWEEK FROM DATE '2022-08-30') AS day_w
SELECT EXTRACT(HOUR FROM DATETIME(2022, 08, 30, 22, 15, 00)) as hour
SELECT EXTRACT(MINUTE FROM DATETIME("2022-08-30 23:40:59")) as minute
SELECT EXTRACT(HOUR FROM TIME "10:25:40") as hour
SELECT
EXTRACT(DAY FROM TIMESTAMP("2022-08-30 15:40:00+00") AT TIME ZONE "UTC") AS day_utc
SELECT
-- DATE
DATE_ADD(DATE "2022-08-27", INTERVAL 3 DAY) AS three_days,
DATE_ADD(DATE "2022-08-27", INTERVAL 1 WEEK) AS one_week,
-- DATETIME
DATETIME_ADD(DATETIME "2022-08-30 20:22:11", INTERVAL 20 MINUTE) as min_later,
-- TIME
TIME_ADD(TIME "20:30:00", INTERVAL 1 HOUR) as h_later,
-- TIMESTAMP
TIMESTAMP_ADD(TIMESTAMP "2022-08-30 22:10:10+00", INTERVAL 2 DAY) AS d_later
SELECT
-- DATE
DATE_SUB(DATE "2022-08-27", INTERVAL 3 DAY) AS three_days,
DATE_SUB(DATE "2022-08-27", INTERVAL 1 WEEK) AS one_week,
-- DATETIME
DATETIME_SUB(DATETIME "2022-08-30 20:22:11", INTERVAL 20 MINUTE) as min_earlier,
-- TIME
TIME_SUB(TIME "20:30:00", INTERVAL 1 HOUR) as h_earlier,
-- TIMESTAMP
TIMESTAMP_SUB(TIMESTAMP "2022-08-30 22:10:10+00", INTERVAL 2 DAY) AS d_earlier
SELECT
-- DATE
DATE_DIFF(DATE '2022-08-30', DATE '2022-08-25', DAY) AS diff_d,
-- DATETIME
DATETIME_DIFF(DATETIME "2022-08-30 15:30:00", DATETIME "2022-07-05 22:45:15", DAY) as diff_dt,
-- TIME
TIME_DIFF(TIME "20:30:00", TIME "19:50:00", MINUTE) as diff_time,
-- TIMESTAMP
TIMESTAMP_DIFF(TIMESTAMP "2022-08-30 11:30:00+00", TIMESTAMP "2022-08-29 20:40:00+00", HOUR) AS diff_tms
SELECT
-- DATE
DATE_TRUNC(DATE '2022-08-30', MONTH) AS trunc_d,
DATE_TRUNC(DATE '2022-08-30', WEEK(WEDNESDAY)) AS trunc_dww,
-- DATETIME
DATETIME_TRUNC(DATETIME "2022-08-30 12:50:00", DAY) as trunc_dt,
-- TIME
TIME_TRUNC(TIME "07:45:33", HOUR) as trunc_th,
-- TIMESTAMP
TIMESTAMP_TRUNC(TIMESTAMP "2022-08-30 05:10:00+00", DAY, "UTC") AS trunc_utc
SELECT
-- DATE
FORMAT_DATE("%D", DATE "2022-08-30") AS f_date,
FORMAT_DATE("%b %d %Y", DATE "2022-09-01") AS f_date1,
-- DATETIME
FORMAT_DATETIME("%c", DATETIME "2022-09-02 12:10:30") AS f_dt,
-- TIME
FORMAT_TIME("%R", TIME "20:10:10") AS f_time,
-- TIMESTAMP
FORMAT_TIMESTAMP("%F %R", TIMESTAMP "2022-08-31 18:45:00+00", "UTC") AS f_tms
SELECT
-- DATE
PARSE_DATE("%x", "08/31/22") AS p_date,
PARSE_DATE("%Y%m%d", "20220830") AS p_date1,
-- DATETIME
PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/31/2022 5:30:55 pm') AS p_dt,
-- TIME
PARSE_TIME("%H", "20") AS p_t,
-- TIMESTAMP
PARSE_TIMESTAMP("%c", "Wed Aug 31 10:40:33 2022") AS p_tms
TIMESTAMP_MICROS(event_timestamp) AS good_time