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 …
Tag: google-bigquery
Join the tables one to many but duplicate the records bigquery
i’m working on bigquery. I have two tables: one for sites and one for site logs. I want to make a query that selects all the sites (without duplicating them) and tells me the last status by date. I also want to know what answer team 1 or team 2 gave for each site. I do left join and duplicate
Calculate number of times a user visited a page in SQL
I have table as shown below. It has 3 columns user, page and timestamp. In the above table I want to calculate number of times a user visits a particular page. Each record in the above table is a hit, so just grouping by column page wont help. I essentially want to count continuous records for a particular pa…
Adding all dates to SQL query for grouped dates
I would like SQL to return all dates, whether or not they are null or 0 from the following query: Current Output: Desired Output: I do not have another table with dates to reference My min should be the first date pulled from the query (in this case, 01/02/22) and then my max should be the most recent date th…
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’}}, &#…
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 answ…
Can’t SET variable in BigQuery | Error: Cannot coerce expression
I’m trying to set variable in order to make further operation. But when I set my first variable on line 2 (“SET(new_user)=( “), I get an error Query error: Cannot coerce expression ( SELECT COUNT(*) FROM… The problem is coming the way I use the SET because my SELECT work without. Answe…
Can a Wildcard Search Be Performed Amongst Fields
Always grateful for the infinite wisdom from everyone on this site. I deal with some massive databases that are often difficult to search in. One of which is a lead database with over 1.4k columns and 3 million records. Sometimes, I just want to query to see if a certain value exists anywhere in the database …
Is it possible in SQL to split the numbers by the delimiter and count them?
I have a column in SQL table with a list of items like below: 99987995|M99987995|99987995|. I want to split the items by delimiter(/|) and then count the number of items. for example I will have 3 items from the list i.e 99987995, M99987995, 99987995. Attached is also an image and what the result will look li…
Inner join with special conditions
Given an hourly table A with full records, e.g.: And a subset B of it, e.g. I want to keep only those records from A that are in B or at most 2hr behind the B subset, without duplication, e.g. How can the result be achieved with an inner join, without duplication (in this case the hours 8&9) and preservin…