Hi I was trying to group data based on a particular pattern. I have a table with two column as below, I need to generate a key column that increment only after rollingsum equals 0 is encountered.As given below I am using postgres, I tried to increment variable in case statement as below But I am getting an er…
Tag: amazon-redshift
How to pull data for past x weeks dynamically in redshift?
I have a below query which I run gives me the single count for previous week which is Week 43. Current week is 44 as of now. As of now the output I see is this which is for Week 43- Now I am trying to make this query dynamic such that it can give me count for past 6
Redshift get Json Keys
I have a table field with a value like this: { “0fc8a2a1-e334-43b8-9311-ce46da9cd32c”: { “alert”: “345”, “channel”: “ios_push”, …
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 try…
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