Background I’m quite new to SQL so apologies if I come off ignorant. After reading through some related threads I am still confused. I got the data from a Firestore export whose endpoint was a Google Cloud Storage bucket. I created a table from the bucket data in BigQuery. Question I want to order the q…
Tag: sql
Excluding same type of transactions within 3 seconds
For example, a table is like below Type Time Stamp Result 1 2021-06-25 14:21:00 A 1 2021-06-25 14:21:03 B 1 2021-06-25 14:21:06 C 1 2021-06-25 14:23:00 D 2 2021-06-25 14:21:02 C 2 2021-06-25 14:21:06 C 2 2021-06-25 14:21:09 D 3 2021-06-25 14:21:06 E And the result I want is as below. If there are a series of …
Oracle 11g SQL Query – Specify which duplicates to Exclude from Select Statement
I have a question about excluding duplicate values with my Oracle SQL query. Using select distinct does not work in this case. I only want to select one row for each event#, the row where Mobile = Yes. Actual Result of Current Query: Desired Result: In this ideal result, one of the rows for Event# 1 has not b…
Redshift SQL: Column Counting Matching Rows Given a Condition within another column
I have a table with two columns, user ids and strings. I want to add a third column that counts the number of strings within the second column that start with the entire string value in any given row. There is only one row per user. The goal is to get the following table structure: Here the count is equal
Checking to see if array field is null or empty?
In snowflake, how can I filter for null or empty array fields in a column? Column has an empty [] or string of values in that bracket, tried using array_size(column_name, 1) > 0 but array_size does not function, Thanks Answer Are you trying to filter them in or out? Either way the array_size should work. a…
Using where clause within a partition
I have huge pageview level table, where each session has multiple pageviews. Session -> Pageviews – 1 : M From the pageviews table, I need to consolidate the data into one row per session with meta data like FirstPageview, LastPageview, #ofvisitstoLogin Firstpageview is defined as the very first page…
SQL: How can I pick a cell value from one table as a condition to select another table
Hi I’m a new learner of SQL. How can I realize this process in SQL or perhaps with python if needed: First, from table1, I randomly selected two results: I need to use the value x and y as conditions to display another table. For instance, using x, I can: What I need is to get the length of table2
Array to string with Django and MySQL
I need to design SQL query in html on Django I have an array in my table like : When I call this array in my page : I have : [‘aa’, ‘bb’, ‘cc’] What do I do if I want : a b c I tried with .split(‘,’) but I’ve already ‘[]’ I tried with for item …
Why am I getting a cross-reference error with my insert/select statement in single database
I have the following INSERT statement, and it works fine without the WHERE clause, but will not work with it. There is a separate database that I have connected to via a dblink connection, and I have created several views that contain data I will need for the database I’m working in. I have the followin…
Combine lead and lag function without overlap
I have this table: I need to get an output like this: I’ve tried a normal lead window function, but that would only show the first ‘Creation’ and not that last ‘Pending’ that I need. Answer Your table has N records (3 in the example provided) when query must return N + 1 (4); let…