SELECT column_name, data_type, description
FROM
`bigquery-public-data`

.austin_311.

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE
table_name="311_service_requests"

Replace_from_other_column = Table.ReplaceValue(#"Changed Type",null, each [Profit] , Replacer.ReplaceValue,{"Technical_test_col"})

#"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {{"c_Pofit_by_state", each List.Sum([Profit]), type nullable number}})

select *
from (
select country_region, date, confirmed, deaths
,LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day
,confirmed - LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) as new_cases
from `bigquery-public-data.covid19_jhu_csse.summary`
WHERE upper(country_region) = 'HUNGARY' AND
date between '2021-09-21' AND (select max(date) from
`bigquery-public-data.covid19_jhu_csse.summary`)
)
WHERE new_cases > 0
ORDER BY 2 asc

select *
from (
select country_region, date, confirmed, deaths, recovered, active
,LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day
,confirmed - LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) as new_cases
from `bigquery-public-data.covid19_jhu_csse.summary`
WHERE upper(country_region) = 'HUNGARY' AND
date between '2021-07-30' AND (select max(date) from
`bigquery-public-data.covid19_jhu_csse.summary`)
)
WHERE new_cases >0
ORDER BY 2 asc

select *
from (
select country_region, date, confirmed, deaths, recovered, active
,LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day
,confirmed - LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) as new_cases
from `bigquery-public-data.covid19_jhu_csse.summary`
WHERE upper(country_region) = 'HUNGARY' AND
date between '2021-07-30' AND (select max(date) from
`bigquery-public-data.covid19_jhu_csse.summary`)
)
WHERE new_cases >0
ORDER BY 2 asc

select country_region, date, confirmed, deaths, recovered, active
,LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day
,confirmed - LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) as new_cases
from `bigquery-public-data.covid19_jhu_csse.summary`
WHERE upper(country_region) = 'ISRAEL' AND
date between '2021-06-01' AND (select max(date) from
`bigquery-public-data.covid19_jhu_csse.summary`)

with prep as (
select 'John Doe' as name union all select 'Jane Doe' union all select 'ABC DEF'
)
select *
,Regexp_extract(name,r'^(?:[^\s]*\s){0}([^\s]*)\s?') as Word0
,Regexp_extract(name,r'^(?:[^\s]*\s){1}([^\s]*)\s?') as Word1
from prep

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, deaths, recovered, active
, LAG(confirmed, 1) OVER (PARTITION BY country ORDER BY date ASC) AS prev_day
from Hu_filter
WHERE date between '2021-03-01' and (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`)
)
select *,
confirmed - prev_day as new_cases
--,round(1-(deaths/confirmed),4) as survival_rate
from filtered
where confirmed - prev_day is not null
order by date asc

with generate_months as (
SELECT * FROM
UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2021-12-1', INTERVAL 1 MONTH)) AS dates
)

select 1 as key, left(cast(dates as string),7) as yearmonth
from generate_months

with ranked as (
select *
,dense_rank() over (order by score desc) as rn
from scores
)

select score, rn as 'Rank'
from ranked
order by score desc

with combined as (
select E.*, D.name as dep_name
from employee E
left join department D
on E.departmentid = d.id
),

ranked as (
select *
,dense_rank() over (partition by dep_name order by salary desc) as rn
from combined
)

select
dep_name as department,
name as Employee,
salary as Salary
from ranked where rn <= 3

with check_countries as (
SELECT count(distinct country_region)
FROM `bigquery-public-data.covid19_jhu_csse.summary` LIMIT 1000
),

India_filter as (
select
country_region,
date,
sum(confirmed) as confirmed,
sum(deaths) as deaths,
sum(recovered) as recovered,
sum(active) as active
FROM `bigquery-public-data.covid19_jhu_csse.summary`
where upper(country_region) = 'INDIA'
GROUP BY 1,2
),

filtered as (
select date, confirmed, deaths, recovered, active
, LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day
from India_filter
WHERE date between '2021-02-01' and (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`)
),

usual_tbl as (
select *,
confirmed - prev_day as new_cases
--,round(1-(deaths/confirmed),4) as survival_rate
from ...

<script>

function GiveBackName() {

let inputvalue = document.getElementById("fname").value
alert(inputvalue)
console.log(inputvalue)

// for TagManager you just need:
// return document.getElementById("fname").value

}

</script>

SELECT
ReportsTo
,count(id) as Members
,round(avg(age)) as `Average Age`
FROM maintable_R70YE
where ReportsTo is not null
group by ReportsTo
order by ReportsTo asc

SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY device.browser
HAVING SUM ( totals.transactions ) > 0
ORDER BY
total_transactions DESC

SELECT max(table_id) as latest_available_day_tbl
FROM `bigquery-public-data.google_analytics_sample.__TABLES_SUMMARY__`

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, deaths, recovered, active
, LAG(confirmed, 1) OVER (PARTITION BY country ORDER BY date ASC) AS prev_day
from Hu_filter
WHERE date between '2020-03-01' and (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`)
)
select *,
confirmed - prev_day as new_cases
--,round(1-(deaths/confirmed),4) as survival_rate
from filtered
order by date asc

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, deaths, recovered, active
, LAG(confirmed, 1) OVER (PARTITION BY country ORDER BY date ASC) AS prev_day
from Hu_filter
WHERE date between '2021-01-01' and (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`)
)
select *,
confirmed - prev_day as new_cases
--,round(1-(deaths/confirmed),4) as survival_rate
from filtered
order by date asc

 with prep as (
SELECT *
, LAG(confirmed_cases, 1)
OVER (PARTITION BY state_name ORDER BY date ASC) AS prev_day
FROM `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 the prev.day
from prep
order by date asc