BigQuery for Data Analysts

Create tables in BigQuery using the cloud console or gui, using DDL or json specs, and build simple and nested schemas, like customers and orders with repeated items.

Create BigQuery tables with the bq command line, using inline specifications or a JSON schema, via Cloud Shell, and verify the orders table schema in the console.

Explore creating tables in BigQuery using DDL and information schema. Retrieve DDL from metadata to generate code for tables like customers and orders.

Learn how to create and use views in BigQuery from the console, including saving queries as views and querying a simple view built from the math functions data set.

Create views using a DDL statement in the query editor by writing create view, naming the view, and defining a select query from a table with a where clause.

Explore schema evolution in BigQuery by adding new columns and preserving historical data, using cloud console, DDL alter table, and BQ update with JSON specifications.

Link the Google Analytics sample public data set in Analytics hub, enable the API, and explore the sessions table to understand schema and data types.

Explore a two-table public data set in BigQuery by examining city bike stations and trips, join on station id, and review personal and project history features.

Explore basic select statements in BigQuery, selecting specific columns from the city bike stations table (station_id, name, region_id, is_renting, last_reported) and learn how data scanned affects query costs.

Use where clauses to filter city bus stations data by bikes and docks, applying and/or logic to refine results and reveal relevant subsets.

Explore using the order by clause in SQL to sort bike station data, from ascending to descending, and combine where with order by to filter and rank results in BigQuery.

Group by clauses organize rows by properties like is renting and is returning, enabling counts and aliases, and revealing patterns across capacity and subgroups.

Learn how to use where, group by, and order by to filter stations by capacity, group by renting status and capacity, count distinct values, and sort results in descending order.

Learn to filter grouped results using the having clause after group by, and understand the proper order of select, from, where, group by, having, and order by, including counts.

Explore how aggregate functions like count, min, max, and average operate on groups by renting status, including aliasing results and controlling decimal precision.

Learn how to perform arithmetic operations on numeric data in sql by selecting capacity and number of bikes available, and computing number of bikes in use and the available ratio.

Use a case statement in sql to prevent division by zero by returning null when capacity is zero, and safely compute available capacity.

Join city bike stations with Citi Bike trips to analyze station details and trip attributes, selecting station id, name, capacity, and trip user type, start time, and stop time.

Master common table expressions in BigQuery to modularize complex queries, define a trips-by-station CTE, and simplify joins and aggregations for readable analytics.

Learn how to insert data with BigQuery SQL, including full rows, partial columns, and inserts from a select, with column order and null handling.

Update data in BigQuery by using the SQL update statement with set and where clauses to modify specific rows, while noting that BigQuery is not optimized for OLTP.

Delete enables removing specific rows from a table; truncate clears all rows. Use a where clause for subset deletes, and truncate for full-table removal in BigQuery.

Learn how to use BigQuery's merge statement to perform insert, update, and delete in one step, using matched and not matched logic to modify or add rows.

Explore how BigQuery handles arrays by creating array values and aliasing them, then flatten and enumerate elements using the Nest function with a cross join to combine with row data.

Learn to use BigQuery array functions to create, combine, convert, and generate arrays, including date arrays and array to string conversions, while working with unions.

Explore querying nested structures in BigQuery using a public analytics dataset, drilling into structs like traffic source and AdWords click info, and selecting totals and visits for efficient analytics.

Learn to query arrays of structs in BigQuery by nesting and unnesting repeated fields, using cross joins, and selecting individual hit fields like hit number, time, and hour.

Explore basic math functions in sql, including truncate vs round, least and greatest, ceiling and floor, natural log, and basic trigonometric functions like cosine and tangent with practical examples.

Create a math functions data set and a table pt1 with integer A, numeric B (precision ten, scale two), and float C; insert sample rows and verify with a select.

Exploit sign and absolute value functions in BigQuery to identify positive, negative, and zero values, convert negatives to positives, and safely assess numeric data.

Learn to use square root and power functions in BigQuery, applying sqrt and raising numbers to powers, including fractional powers for roots, with attention to numeric overflow.

Learn how to sample large data sets with rand and mod in BigQuery; generate random numbers, cast to integer ranges, and achieve deterministic subset selection with mod.

Learn how to perform safe arithmetic in BigQuery by using safe divide to avoid division by zero and overflows, and apply safe add, subtract, multiply, and negate operations.

Learn to cast data types in BigQuery, converting integers to floats, floats to integers, and strings to date times. Format dates and timestamps, including current date and timezone information.

Learn to manipulate dates in BigQuery with date functions: extract day, month, year and other parts; create, add or subtract dates; compute date differences; and truncate to a date part.

Explore date time functions in BigQuery, creating and formatting date time values, extracting hour, week, and quarter, and performing add, subtract, and diff operations for time-based analyses.

Learn time functions in BigQuery to create current times with now and optional time zones or UTC. Extract time parts like hour and second, and add, subtract, or compare times.

Explore timestamp functions in BigQuery, including current_timestamp, extract, string conversion, timestamp construction, and timestamp arithmetic, plus time zone handling for UTC and Pacific time.

Calculate the Pearson correlation between trip duration and birth year using the CORR function on the NYC city bike trips dataset. Shows near-zero results, indicating no relationship.

Use standard SQL functions in BigQuery to compute covariance, with covar_pop for population data and covar_samp for samples; covariance measures joint variability and is not normalized.

Explore how to measure data spread with variance and standard deviation using BigQuery functions, choosing between sample and population calculations for distributions like age or trip duration.

Explore basic string functions in BigQuery for data analysts, including length, ascii, character, and case transformations (upper, lower, cap), reverse, concatenation, trimming and padding, and sound x.

Trim and pad strings with ltrim, trim, pad, and iPad to length 12; demonstrate left and right trimming, and verify results by string length for ETL data.

Use the split operator to parse a delimited string into an array by comma, demonstrated with a common table expression producing id, manufacturer, and component type.

Explore string manipulation in BigQuery with left and right for substrings, starts with and ends with for boolean checks, contains substring and in string for searches, and substring for extractions.

Explore how regular expressions enable pattern matching in strings using regex contains, regexp extract, and replace in BigQuery. Learn when to use regex versus the search function for large text.

Explore how window functions perform per-row calculations over related rows using the over clause, partition by department, and order by salary to rank salaries within each group.

Explore lead and lag window functions to look ahead and behind within partitions, ordering by trip duration within each bike id to reveal next and previous values.

Use the first_value window function to compute a bike's shortest trip duration and, by ordering trip_duration within each bike ID partition, show its longest duration.

Explore numbering functions in BigQuery, including rank, dense_rank, percent_rank, cume_dist, and ntile, applied within a bike trips partition by bike id and ordered by trip duration.

Learn to use moving average functions in BigQuery with window functions, partitioning by bike ID and ordering by trip duration, with three preceding and three following rows.