Select
DATE,
Region,
Small_Bags,
Large_Bags,
XLarge_Bags,
Total_Bags,
(Small_Bags / Total_Bags)*100 as SB_percent
From avocado_data.avocado_prices
WHERE Total_Bags <> 0 or Total_Bags != 0
-- values we are calculating does not equal to 0
How to use TEMPORARY TABLE
With trips_over_1_hr as
(
SELECT *
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
tripduration >=60
)
-- this is a description not part of the code
##Count how many trips are 60 minutes long
SELECT
count(*) AS cnt
FROM
trips_over_1_hr
HOW TO APPLY TEMP TABLE
-- marketing team wants to write a blog post that “congratulates” their most-used bike on being so popular. They want to include the name of the station that the bike is most likely to be found.
With longest_used_bike as (
SELECT
bikeid,
sum(duration_minutes) as trip_duration
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY
bikeid
ORDER BY
trip_duration DESC
LIMIT 1
)
## station from which bike leaves the most frequently
SELECT
trips.start_station_id,
count(*) as trip_cnt
FROM
longest_used_bike as longest
FULL JOIN
`bigquery-public-data.austin_bikeshare.bikeshare_trips` as trips
ON longest.bikeid = trips.bikeid
GROUP BY start_station_id
order by trip_cnt DESC
WITH
SELECT INTO
SELECT INTO
CREATE TABLE
