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
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 page only
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 that
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
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. Answer Depends on what your real needs – i
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 within ANY field. (Like
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 like. For every group in
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 preserving