I have a table field with a value like this: { “0fc8a2a1-e334-43b8-9311-ce46da9cd32c”: { “alert”: “345”, “channel”: “ios_push”, …
Tag: amazon-redshift
Min and max value per group keeping order
I have a small problem in Redshift with with grouping; I have a table like following: INPUT VALUE CREATED UPDATED ———————————— 1 ‘2020-09-10’ ‘2020-09-11’ …
How to join tables when left join does not generate null values for the dates
I have three tables as shown below: 1. kit_test tk tk.id tk.first_name tk.last_name tk.dob tk.registered_dt SD007 Aarushi Sharma 2/23/1987 10/5/2020 SD008 Camden Howard …
How do I sum until it meets a criteria?
I have the following data set for an user. I want to merge the False flag rows to the latest True row. initial set: user input_date starting_balance deposit flag user1 2020-08-20 100 …
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.
Aggregating two values in same select statement. Second aggregation is decreasing in value for each row for some reason
I’m currently trying to aggregate two values simultaneously in one select statement; however, the second aggregated value is decreasing for some reason. I know what I’m doing is wrong, but I don’t understand why it’s wrong (assuming it’s the very last code block). Mainly just trying to better understand what’s going on, and why it’s happening. I already have a
How can I pass a list, array or string to be separated as a parameter to redshift
I’m trying to write a simple query with an in clause like so: I need to be able to pass the values in the in clause as a parameter, the number of values in the in clause are variable and could be one or thousands depending on the user input. In other sql databases I have solved this problem by
SQL how to find continuous count of rows that exceed a value over time
I need to figure out how to query this table such that I find out the continuous count for each ID in which the value is equal or above 1 and the timestamp1 is bigger than timestamp 2. So for example: ID 1 will have a count of 2 because the 3rd row is 0 and even though the 4th
Redshift If TableA contains data then TRUNCATE TableB and INSERT TableA into TableB ELSE do nothing
I am trying to create a query in Redshift but I am struggling to ether create the correct logic or to get it to run full stop. I have 2 tables TableA and TableB. If TableA contains data, then wipe TableB and insert the data from TableA. Pseudo: Obviously this wont work so do I need to do something with
Amazon Redshift join variable duplicated with select *
I’m trying to create a new table in Redshift The code used to create a new table works on it’s own but when I wrap it in create table it stops working Invalid operation: column name “Number” is duplicated; Any ideas what’s happening? I don’t want to specify every single column of b just so i can omit b.numbe Answer