ID Status Date 1 Online 2022-06-31 1 Offline 2022-07-28 2 Online 2022-08-01 3 Online 2022-07-03 3 Stationary 2022-07-05 4 Offline 2022-05-02 5 Online 2022-04-04 5 Online 2022-04-06 The output I’m looking for in SQL/BigQuery ID Status Date 2 Online 2022-08-01 5 Online 2022-04-04 5 Online 2022-04-06 So wh…
Tag: google-bigquery
Google Bigquery SQL with clause `where 1=0` is not returning correct schema
I recently added a new column in my BigQuery Table. The following code snippet is used in legacy code to determine the table schema But the problem is that it is not returning the newly added column in the df. Although when I use some other condition like 1=3 in where clause or limit 0 then it returns the cor…
SQL aggregation based on time and location for Sankey graph
I have a situation where I’m trying to get data to populate a Sankey graph. I have the data with timestamp and the location of the person when captured by the system. The normal case is when a person location changes and in that case, “from” should be that location and “to” shoul…
SQL – create new col based on value of other column group by third column
I have this table Id item type A itemA1 X A itemA2 X B itemA1 X B itemA2 X B itemA3 Y And i would like to create new indicator which contains the information about if the Id contains only item of type X or only tpye Y or both like this : Id Indicator A Only X B Both
create as many new columns as there are months and fill them by sum of quantity
I have these data : Date Item Qty 2022-01-01 A 10 2022-01-01 A 30 2022-01-01 B 40 2022-02-01 A 20 2022-02-01 C 10 I would like to create as many new columns as there are months and sum the qty group by Month and by item like this : Item JAN FEB A 40 20 B 40 0 C 0
Fill Null Values with Last Previous Value and add 1 as a continuous integer for every value going forward – Big Query
I have these two columns in Big Query: budget_id and activity: budget_id activity region execution window 000507_Corporate 507 Corporate 2022 000508_AMERICAS 508 AMERICAS 2022Q2 NULL NULL c b NULL NULL c b The budget_id comes from a Google Sheet which is manually inputted by a stakeholder. I’m trying to…
Apply lowercase in specific column for certain group of countries in BQ?
I need to apply lowercase to product_name column for countries like FR, ES, but for the rest of them CA, IL, DE should be without any changes. I’m tryin with this query, but something doesn’t work: Answer Your query probably returns null for other countries, so as @forpas suggested in comments add…
Count records based on a repeating field while returning other values
I have the following query and a sample output uid creation_date activity_date feature1 feature2 feature3 id1 date1 date1x asdf sfsdfd sdsdf id1 date1 date1x asdf fasaa asdas id1 date1 date1x asdf sfsdfd asdas id1 date1 date1x aadf afdsf adfad id2 date2 date2x aadf afdsd asdas id2 date2 date2x aadf adsfsdf sd…
Correct BigQuery SQL to get value from previous partition
I am trying to use a window function to get a value from the previous partition/window frame (rather than from some other row in the same partition). I don’t fully understand how I can do this but have tried various combinations of using: partition clause but with LAG() function omitting partition claus…
Google BigQuery: How to filter out rows by a particular column’s value frequency
Say that I only want to return rows where a column value occurs at least twice. I would do something like That works for just one column, but if I want to do return several columns but only have the filter apply to one column, it doesn’t work. My attempt is Which gives a “ColumnB which is neither …