I’m working in bigquery with this mock data: and what I want to get out is the active lineups. The output would look like so: With the logic behind it being: I’ve almost cracked it using some sort of lead(valid_from) between valid_to and valid_from and, doing a case when to make it 1 if its a new lineup 0 otherwise,
Tag: google-bigquery
Why is json_extract_scalar returning null
I have a fairly simple string-formatted json column in a BigQuery database I am trying to flatten. id relationships 1 {‘ownerObject’: {‘data’: None}, ‘investmentObject’: {‘data’: {‘type’: ‘assets’, ‘id’: ’40’}}, ‘securityObject’: {‘data’: None}, ‘segmentObject’: {‘data’: None}, ‘dataItemObject’: {‘data’: {‘type’: ‘dataItems’, ‘id’: ‘13161’}}, ‘scenarioObject’: {‘data’: {‘type’: ‘scenarios’, ‘id’: ’13’}}} Running: Gives me: id Invest_Type 1 null I would expect the column Invest_Type
Hierarchy Queries in BigQuery
I have a data from ControlM (scheduler) this way I saw many solutions where they have started from first node and descended to last node. But that is likely to fan-out with this data. What I am looking for a fan-in solution, where I can start with any end node and come towards first node In this case the answer
Return only ALL CAPS strings in BigQuery
Pretty simple question, specific to BigQuery. I’m sure there’s a command I’m missing. I’m used to using “collate” in another query which doesn’t work here. Desired return: [email protected],[email protected] Answer Consider below If applied to sample data in your question – output is In case you want the output as a comma separated list – use below with output You can
Sort COUNT(CASE WHEN) results
I am taking a database of statuses and creating the statuses as columns in order to count how many records from a network exist in each status. I’d love to sort the results based on the Partnered column DESC, but I can’t figure out how or where to do that?? Here’s my code: Here are my results so far. row
Is there a way to restrinct rolling window from summing if not 2 behind?
I want to sum over 3 rows (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) using SQL ROLLING WINDOW: The issue is that if try to use the statement ROWS BETWEEN 2 AND CURRENT ROW, it will provide SUM even if I have only ONE preceding row, and I would want to have result as NULL / 0 if there’s not
Getting a Monthly Date Range
How can you make a date range in a big query? A date range starts from 29th of the month and ends with 28th of the next month. It should be like this Date | Starting Date | Ending Date 03-13-2020 | 02-29-2020 | 03-28-2021 06-30-2020 | 06-29-2020 | 07-28-2021 01-01-2021 | 12-29-2020 | 01-28-2021 11-11-2021 | 10-28-2021 | 11-29-2021
BigQuery – Scalar subquery produced more than one element –
I have this query that helps me to find separate key words within strings (very useful with utm_campaign and utm_content): For example: if I have a umt_campaign like this: us_latam_mkt_google_black-friday_audiencie-custom_NNN-NNN_nnn_trafic_responsiv The query from above will help me to separate each word with a _ in between. So I’ll have a result like this: utm_campaign country product budget source campaign audience
bigquery transpose and concatenate for each record
I want to achieve the following transformation. I have last_name stored in a repeated record as follows. data before transformation I want to achieve the following. data after transformation Example with sample data created. I’m not sure either if I should store it as an array instead of a concatenated field but it would be good to know how to
Unnecessary blank row in SQL query
Aim was to write a SQL query to return ride details from a public database. While going through a data analytics course, I stumbled across this problem. the code used is correct as the instruction provided was the same however the result I obtained was different. The first row returned a blank row with just the number of trips mentioned.