Stack Overflow
Stack Overflow

Questions tagged [legacy-sql]

Questions related to using "Legacy SQL" for Google BigQuery

78 questions
2
votes
2 answers

Big Query: Ambiguous column name

Okay, I am new to SQL and Big Query and got an ambiguous column name error. I have checked the other answers on stack overflow, but could not find/understand an answer to my problem. So I get: Error: 2.40 - 2.68: Ambiguous column name subreddit. For…
AAP
  • 21
  • 1
  • 3
2
votes
2 answers

Google Bigquery Legacy SQL - How to set week number start to Monday?

I've heard the UTC_USEC_TO_WEEK() function in legacy sql allows you to set the start week to monday (and not sunday which is the default). Basically, I want the below date queried to return 1 and not 2. select week('2018-01-07') -- returns 2 I've…
AK91
  • 671
  • 2
  • 13
  • 35
2
votes
2 answers

How to count number of a particular event before another event in SQL Bigquery?

I have a table containing date and events and users. There is event named 'A'. I want to find out how many time a particular event occurred before and after event 'A' in Sql Bigquery. for Example, User Date Events 123 …
VSR
  • 87
  • 2
  • 18
2
votes
4 answers

Count number of events before and after a particular event in SQL?

I have a table containing date and events. There is event named 'A'. I want to find out how many events occurred before and after event 'A' in Sql Bigquery. for Example, User Date Events 123 2018-02-13 D 123…
VSR
  • 87
  • 2
  • 18
2
votes
1 answer

Working with arrays with BigQuery LegacySQL

Each row in my table has a field that is an array, and I'd like to get a field from the first array entry. For example, if my row is [ { "user_dim": { "user_id": "123", "user_properties": [ { "key":…
Gabi
  • 413
  • 2
  • 8
2
votes
2 answers

I want to join two tables with a common column in Big query?

To join the tables, I am using the following query. SELECT * FROM(select user as uservalue1 FROM [projectname.FullData_Edited]) as FullData_Edited JOIN (select user as uservalue2 FROM [projectname.InstallDate]) as InstallDate ON…
VSR
  • 87
  • 2
  • 18
2
votes
1 answer

date comparison in BigQuery

I am trying to filter out certain dates in my query. The line of code I am using in my query is as follows: (date(date_add(date_time,-1,'year')) < date(date_add(current_date(),-1,'year')) ) OR year(date_time) = 2017) The idea is to get all data YTD…
kshoe94
  • 389
  • 2
  • 4
  • 11
1
vote
1 answer

BigQuery - Get percentage of null values in column grouped by another

I have a table like store_code country_code timestamp null FR 1234567890 123 FR 1234567890 456 GB 1234567890 789 GB 1234567890 I would like to get pct_null_store_code country_code 0.5 FR 0.0 GB I know how to do it…
Mawu Ena
  • 11
  • 3
1
vote
1 answer

How to format a float64 date returned by a bigQuery job to timestamp/date?

What I am trying to do is create a job using this query: SQL Query: SELECT execution_id, top_level_execution_id, process_id, atom_id, message, node_id, pay_load, environment_name, status, date_created, custom_message, dynamic_field1, dynamic_field2,…
Cristian Baciu
  • 133
  • 1
  • 2
  • 15
1
vote
1 answer

How to group sessions that have timestamps close to each other?

My scenario requires me to look at sessions that are less than 60 seconds apart as the same session. data is like below. Min_Timestamp Max_Timestamp Device_ID Session_ID Prev_Max_Timestamp …
Colton Pan
  • 25
  • 1
  • 6
1
vote
1 answer

Converting Legacy SQL Query to Standard SQL

I am looking to convert the below Legacy SQL Query to Standard SQL. The issue I am having is that I need to unnest two tables (labels and credits). How can I convert this query? Thanks! I run into a "Scalar subquery produced more than one element"…
Simon Holstein
  • 123
  • 1
  • 6
1
vote
2 answers

Input calculates to invalid time: 24:00:00 in big query

I am running a query to bring time in 24 hr format but when i run query and it calculates time over 23:59:59, instead of going over and changing time to 00:00:00 it gives me error saying Input calculates to invalid time: 24:00:00. …
Akki
  • 59
  • 1
  • 7
1
vote
1 answer

Concat time in hr:min for Bigquery

Tying to concat the time together in big query but it is not letting me use it. IF(CAST(TRUNC(timestart/60) AS INT64) <= 12, CAST(TRUNC(timestart/60) AS INT64), CAST(TRUNC(timestart/60) AS INT64) - 12) AS hour, MOD(timestart, 60) AS…
Akki
  • 59
  • 1
  • 7
1
vote
1 answer

Big Query - different number of users when using legacy and normal sql

I have written a query in Google Big Query and want to get the same number of users I see in Google Analytics. I used Legacy and Normal SQL and got 3 different users numbers while the sessions were the same. What did I do wrong, or does anyone have…
Pierre W
  • 21
  • 5
1
vote
1 answer

bigquery legacy sql POSITION() function in standard sql

I try to translate a legacy query into a standard SQL query in bigquery, but I don't find the function position() in standard SQL.
APF
  • 11
  • 1
1
2
3 4 5 6