I have data in table as below: Account Id Amount Start_date End_date 123 10 2023-11-16 17:00:00 2023-11-16 18:00:00 123 10 2023-11-16 02:00:00 2023-11-17 02:00:00 123 20 2023-11-17 17:00:00 2023-11-17 18:00:00 123 30 2023-11-18 02:00:00 2023-11-20 02:00:00 123 10 2023-11-18 17:00:00…
I want to move data from our dynamo DB to redshift, but I want to do this is real time as we are currently under a migration and still using the dynamo DB, any suggestions would be greatly appreciated. I…
Alright. I have a table that has SUPER type fields. These fields hold values like below: id mycol --------------------------------- 1 [{"Title":"first"},{"Title":"Second"},{"Title":"Third"},{"Title":"fourth"}] 2 [{"Title":"some"},{"Title":"thing"}] 3 [{"Title":"else"}] I want to extract the JSON values and concatenate them with commas in each row.…
I have a Redshift Serverless database that I need to delete. When I run the command: drop database [my_database_name] an error is returned: SQL Error [55006]: ERROR: database "[my_database_name]" is being accessed by other users The error makes sense. I…
I have a table within Redshift with a self-referential ParentID to join back to the ProductID to create a hierarchy. Basic structure (full table is c6k records, with arrays up to 90 IDs): CREATE TABLE sandbox.productmk2 ( productid integer ENCODE…
I have generated a table that contains dates from 2000-01-01 to 2039-12-31 (ISO format). Calendar contains leap years. Now based on this I want to create another table with some transformations. I have a problem with two columns. Fiscal_year_day_nr and…
I am looking to group users into separate groups based on when they bought something. A new group starts after it's been more than 90 days when the user last bought an item. Example of what I am looking for:…
I'm having trouble getting the IAM role setup to work for a Redshift scheduled query. I created a role redshift-scheduled-query that looks like: { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "events.amazonaws.com", "redshift.amazonaws.com" ] }, "Action":…
I am trying to replace multiple whitespaces from a column in Redshift(Postgres) Query used : Select replace(col_name,' ',' ') from table; above query is not working for multiple whitespaces only. I have tried replacing other characters in the same query,…
How do I remove square brackets and commas from ["000-000-0000-000","000-000-0000-000","000-000-0000-000"] in sql amazonredshift I tried ltrim it didn't work. It prompted me to use cast function. Not sure how should I use cast function here.