Multi table create statements from nested CTEs

Question

I am rather new to Bigquery. I need to find ways to do the below in as little queries as possible. Ideally one (so it could be one Scheduled query than multiple ones).

The goal is to track errors and error rate for operations and analytics.

Let say I have a table called products, it contains columns: product, maker, color, material I want to:

1. a) find all products, where material is metal - this is my check Scope (denominator)
2. b) from that selection (means material = metal), I want to find where color is red - and load all results to table - metal_red (this is my error list, numerator)
3. c) calculate the ratio of (metal_red / metals_all) and write results to the table red_metal_ratio (it should have count_metals_all, count_metals_red, red_metals_ratio). This is my error ratio.

It is important to save metal_red results into the table, as this error list will be need to be tracked and investigated. It is also important to write the ratio (red_metal_ratio) results, as this is a trackable KPI.

If possible, I would like to avoid having to repeat "where" statements (meaning that avoid having CTEs with overlapping where clauses ). Not sure if/how that is possible at all.

Appreciate your help :) Managed to achieve that with multiple CTEs, but also had to repeat Where clause, which is not ideal in terms of typing :)

best, Dovile

Answer 1

You cannot create multiple tables in one statement, but if these are all trackable APIs why are you doing table creates in the first place? Shouldn't you be doing inserts into tables you already created?

In any case, you can "chain" the creates such as:

create table metals_all as select * from products where material = 'metal';

create table metals_red as select * from metals_all where color = 'red';

create table red_metals_ratio as 
    with 
        (select count(*) count_metals_all from metals_all) as ma,
        (select count(*) count_metals_red from metals_red) as mr
    select
        ma.count_metals_all,
        mr.count_metals_red,
        mr.count_metals_red / ma.count_metals_all as red_metals_ratio
    from
        ma join mr on 1 = 1