“CityJail” schema. I am supposed to find each appeal that has a less than the average number of days between the filing and hearing dates. Here is my code: I get an error: ORA-01427: single-row subquery returns more than one row 01427. 00000 – “single-row subquery returns more than one…
BigQuery get row above empty column
Data: What would be the best way to get the row right above the empty cell? For example, I’d like to filter out row 1 and 3 based on the fact that column_b in row 2 and 4 are empty (in this example). A simple where statement does not work unfortunately, but how would it be possible to get the
Finding daily registered users
I have a table which includes the information of player_id, first_timestamp, last_timestamp, and date, etc. So I have initially 10 payers on 2020-07-08, and then 18 players on 2020-07-09, some of the players from previous day might appear on 2020-07-09 again. And I’m trying to find the new players regis…
WHERE a AND b returns just WHERE b
I have 2 tables which I want to join and return only those records which: Have the same value with other records in one of the columns. Have certain values in other column. These are my requests: List only those records which have the same value with other records in grnz column: List only those records which…
SQL subquery to find overlapping customers
I’m working on writing a SQL query that would allow me to find customers who bought a certain variety of a product during specific sale dates and then subsequently bought a different product within a different date range. I want to use a subquery in the from clause to search for the second group of cust…
Count NULL values by column in SQL
Suppose I have the following table: How can I count the number of NULL values by each column? My final result would be: Answer You can use union all: Redshift is a column-store database, so there probably is not a more efficient method.
Learning to use execute block in Firebird with a simple example
I haven’t used yet execute block in Firebird, but Im struggling to learn how to use it. I am trying to learn from a simple example and then will expand to a more complex situation. Suppose I have 2 tables like below: I have the following code. Basically, I want for every client in the client table to ca…
How to loop JSON array in postgres and access keys of the JSON?
I have a custom json with several key-value pairs. I want to loop over the length of the array, and access each of their keys, and insert into the table. The problem I am facing is during loop the query is unable to access the value. Answer the loop over jsonb array will look like this
MySQL combine counts on columns from two tables
Is there a way to combine these two queries into one where table1.column1 = table2.column1 so that it would return the following? Answer Use subquerys and INNER JOIN
How to group rows in SQL with a special condition?
I have a table like this in my database: I have to write a SQL-Query that selects every month that does not contain “content” at least once. And it should only give out one line. So the output should be like this: How can I accomplish that with a SQL-Query? I tried to do that with the group by fun…