Skip to content
Advertisement

Tag: amazon-redshift

partition by customer for distinct items

select customer_id, row_number()over(partition by customer id order by date) as rn from table How to get same rn when Item Id is the same? Below did not work: #1 select customer_id, row_number()over(partition by customer id, Item Id order by date) as rn from table Answer We can try to use DENSE_RANK instead of row_number window function If the optional PARTITION

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: ^ – start of string (([^,]*,){4}[^,]*) – Group 1 (1 refers to this part of

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

Advertisement