Given the following data sample, I’m trying to perform a running sum() isolated to date, account_no, service. Hoping someone here can help. I started with this code: The above worked as expected but then I wanted to see if I could add in other data features such as account_name and service such that I c…
Tag: amazon-redshift
Why am I unable to select all records in one table that don’t exist in another table?
I have two tables like: Desired output: I want to select all records in col1 of table_1 that aren’t in col1 of table_2. In actuality, table_1 has 65000+ rows and table_2 has around 2000 rows. My query: This returns no records. However, the reverse works as intended when I select all records in table_2 t…
How to join one column multiple times to many columns? – SQL
I have two tables like: I want an output like: I think this will require JOIN and UNION ALL, but I can’t figure out how exactly to put everything together. I’m confused because we’ll need to join multiple times per row. Would appreciate any help, thanks Answer You can do: However, I would sa…
Concatenate values of two consecutive rows SQL
I want to write a SQL query to concatenate row values of 2 consecutive rows as described below: I have the below table: I want to have another column as below: The concat value is the concatenation of serial_no value in row 2 with the serial_no value is row 1, serial_no value in row 3 with the serial_no value…
Efficient way to join multiple columns to the same column? – SQL
I have a bunch of tables combined together, which each have a column containing some form of user_id. This leads to 12 user_id columns in total. I want to join each of these user_id columns with a user_id column in a mapping table in order to retrieve the username for each of these user ids. So (assuming I ha…
SQL, extract everything before 5th comma
For example, my column “tags” have I’m trying to return everything before 5th comma. below is the result example I’ve tried below code but it’s not working. Answer You can use See the regex demo. The REGEXP_REPLACE will find the occurrence of the following pattern: ^ – star…
SeedTable.SomeColumn +1 in a recursive CTE not working as expected
I’m trying this query in Redshift: but I keep getting this error: (500310) Invalid operation: column “level” does not exist in child, parent; 1 statement failed. What am I doing wrong? According to the documentation I found here, Redshift accepts recursive and this is the way to go about it:…
Redshift: Support for concurrent inserts in the same table
I have a lambda code that fires some insert queries to the same Table concurrently through redshift data api. Considering such queries will be fired concurrently, does Redshift apply a lock to the Table for each insert? Or does it allow parallel insert queries in the same table? I’m asking because postg…
Redshift table access history
I wonder are there any way to get table access history in Redshift cluster? Our cluster has a lot of tables and it is costing us a lot. I would like to discover what specific tables have not been accessed for a given period and then I would drop those tables. Are there any ways to get table access history?
Pivot and Sum in Amazon Redshift
I have a following tables table1 table2 first I’d like to join table2 as follows and then join table2 with id and pivot and sum up them I guess it is very complicated, are there any way to achieve this? In Redshift it seems that there is no way to pivot them. Thanks. Answer This looks to be a pivot