CODEWITHSUNDEEP
sqlgoogle-bigquery
Dervin Thunk
Dervin Thunk

Reputation: 20119

How do I reference variables in the WHERE portion of an SQL clause for bigquery?

I found this code here... besides syntax problems (a missing comma, using functions as fields, at least...), it gives me an error in Google BigQuery with the "date" in the WHERE clause. Not sure if this is an error in BQ or just not possible to "guess" date yet... 

CREATE TABLE count_unique_subscribers_per_region_per_day AS (
    SELECT date(calls.datetime) AS date,
        cells.region AS region
        COUNT(DISTINCT msisdn) AS count
    FROM calls
    INNER JOIN cells
        ON calls.location_id = cells.cell_id
    WHERE date >= '2020-02-01'
          AND date <= CURRENT_DATE
    GROUP BY 1, 2

);

How do you reference date in the WHERE clause?

Upvotes: 0

Views: 285

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL

Just simply reference date(calls.datetime) directly as in example below 

CREATE TABLE count_unique_subscribers_per_region_per_day AS (
    SELECT DATE(calls.datetime) AS date,
        cells.region AS region
        COUNT(DISTINCT msisdn) AS count
    FROM calls
    INNER JOIN cells
        ON calls.location_id = cells.cell_id
    WHERE DATE(calls.datetime) BETWEEN '2020-02-01' AND CURRENT_DATE()
    GROUP BY 1, 2

);

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

If you look at the schema for the tables in this project (https://github.com/Flowminder/COVID-19/blob/master/core_tables.sql), you will see that the table calls has a column called date. A query like the one in the question will work, as long as the table calls has a column date.

If the error you see says Unrecognized name: date at, then your table calls might not have a column date.

As you can see here, the query in the question can be run without problems, if the tables have the right data. Oh, and I had to add a semi-colon too:

WITH cells AS (
  SELECT 1 cell_id, 3 region 
)
, calls AS(
  SELECT CURRENT_TIMESTAMP() datetime, CURRENT_DATE() date, 2 msisdn, 1 location_id
)


SELECT date(calls.datetime) AS date,
  cells.region AS region,
  COUNT(DISTINCT msisdn) AS count
FROM calls
INNER JOIN cells
    ON calls.location_id = cells.cell_id
WHERE date >= '2020-02-01'
      AND date <= CURRENT_DATE
GROUP BY 1, 2

enter image description here

Upvotes: 1

Related Questions