I have the following sample data with 3 columns, i.e., account_id, interval_start, interval_end. And I want to create a reactivated column with the given output in the following table. For each account_id, reactivated column should start with a 0 value…
In AWS Redshift, I am using json_extract_path_text function to parse simple Json. I deliberately try to find non exists element in the Json so the function should return NULL. If I use the function in 'standalone' select, the function return…
I'm running a case when exists to try to filter out anamoly data from my table, but I am running into a unsupported correlated query error. I have a table of anomalies for different metrics and I want to null…
I am trying to add another column called session_id. I want to rank according to the condition that if the time difference between the date_time is more than 30 minutes, then that will be counted as another session. Here is…
With this following schema CREATE TABLE test ( id INT , is_true BOOL ); INSERT INTO test (id,is_true) VALUES (1,FALSE); INSERT INTO test (id,is_true) VALUES (2,FALSE); INSERT INTO test (id,is_true) VALUES (3,TRUE); INSERT INTO test (id,is_true) VALUES (4,FALSE); I make…
I don't think I've every asked such a simple question :( But how does one List Roles in Redshift ? For groups you simply select from pg_groups. Not so for roles, and surprisingly little documentation. What am I missing ?
I would like to create a table that will be automatically sorted by a column, but with null values last. I thought i could do it with sortkey like this, but when querying the table it still comes back just…
I am trying to remove the Parenthesis and its contents after a comma for an Employee Name Field. I cant seem to get the syntax right to remove special characters and numbers AFTER the first comma. Then i would like…
I am loading CSV files to Redshift. I first run the COPY command to a staging table, then normalize the account data into a separate lookup table. BEGIN; INSERT INTO AccountLookup (Account) SELECT DISTINCT Account FROM Staging db WHERE NOT…
Im trying to extract an array using array_position to return the position of a specific string. Unfortunately, redshift doesn't have this function.