Imagine, you are looking at a social network graph of millions of users. Imagine Facebook users who are in different Facebook Groups. Let me give the following example: We start with Jeff. Jeff has a degree of connection with himself of 0. Rohit is in the same Facebook group as Jeff, so his degree of connecti…
Tag: sql
How to Unpivot a Struct in BigQuery?
I have a table with an ID and then a struct. I want to turn each element of the struct into a new row with the struct field name being a value in the column Period and the value being the structures value. See table below Query that generated the table: Current data I tried this: But I get this
SQL – updating column based on next row value
I have a table, and the requirement is to update column ‘val’ based on the value in the column ‘val’ in next row (ordered by by id). Table is shown below : I’m able get the select query to get the next value, however the update query is not working. Here is the select query (work…
SQL: How to COUNT the number of keys that appear in a certain number of rows
I have the following two SQL tables dog and toy: dog id gender 1 2 2 2 3 2 4 1 toy toy_id dog_id 1 1 1 2 2 1 2 2 We want to find out how many female dogs have a certain 0 toys, 1 toy, 2 toys… A value of 2 in the gender column indicates the
How to remove and carry forward month prior when running balance decreases?
I have the following data: client_id balance_month balance 100000000 September 1,2021 12:00 AM $7000 100000000 August 1,2021 12:00 AM $7000 100000000 July 1,2021 12:00 AM $6000 100000000 June 1,2021 12:00 AM $3000 100000000 May 1,2021 12:00 AM $20000 100000000 April 1,2021 12:00 AM $1000 100000000 March 1,202…
SQL query to select all tuples that do not have a specific attribute at all
I have a table (shown below) that maps users to the fruits that they like. This table is a result of a a complicated SELECT statement, and now I am left with specifying the WHERE condition. I would like to select all users that do not like the fruit “Orange” at all. U.id F.fruit 1 Apple 1 Orange 1…
BigQuery SQL reshape four columns into one column with name of column as value
I currently have a table that looks like: Is it possible to reshape my data into: Where the name of the column equal to 1 becomes the value of code in the new reshaped dataset. I have already presorted the dataset such that there will be no case where more than one column is equal to 1. Answer Consider below
Can I connect a openCart project with mobile app? [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed last year. Improve this question my English doesn’t well, I am sorry I have a open Cart project this project have own dat…
Kubernetes Whitelist Alternative SQL Server
We have a SQL Server hosted on a cloud VPS and a kubernetes cluster hosted on Digital Ocean. I am using their kubernetes offering. Our server the SQL Server sits on, has firewall rules to only allow whitelist IPs to connect to the SQL Server. So currently we add the IPs to each of the kubernetes nodes so they…
Efficient syntax to update 5K rows in BQ table
I’m trying to update ~5K rows in bq using python client. Here is my current try: and How can I map the account id list to a string as follows which seems more efficient(?) UPDATE mytable SET somefield=( CASE WHEN (id=100) THEN ‘some value removed’ WHEN (id=101) THEN ‘some value removed…