I am trying to return the foretasted value per item, per warehouse, per day and then add them up for the week. I am pulling from two tables depending on the demand date, but the issue is that both tables have a “creation_date” column with timestamps, so it’s creating multiple raw_forecast en…
Tag: amazon-redshift
Convert character to number, like +00005 to 5
We have the fixed format data and it has the number with plus/minus sign, like +00005 or -00005. We’d like to convert it to number but Redshift seems not to allow implicit conversion. So, I temporarily use to_number(replace(<numbercolumn>, ‘+’, ”),’99999′) But I guess…
Can’t alter column type to a shorter VARCHAR in Redshift. Getting “target column size should be greater or equal to current maximum column size”
I’m trying to run in Amazon Redshift, but I’m getting this error: SQL Error [500310] [0A000]: Amazon Invalid operation: cannot alter column “column_name” of relation “table_name”, target column size 256 should be greater or equal to current maximum column size 879; I’…
Creating date segments in redshift SQL
I’m trying to create segments of date based on a flag. Below is the table I currently have order by start_date,end_date. ID start_date end_date flag 9659 5/18/2012 5/18/2012 0 9659 5/18/2012 5/18/2012 0 9659 6/8/2012 6/8/2012 0 9659 6/8/2012 6/8/2012 0 9659 8/25/2012 8/25/2012 1 9659 8/27/2012 8/27/2012…
Filter out specific value without excluding the NULL rows
I have the following table and I want to filter out a specific value but keeping the null (empty) rows. I tried different ways but the query kept filtering out the null rows. For example, I only want to filter out the rows with the orange, the output table should be: I also tried with OR All of the returned
addition within sum case when
How to sum all the customers in DS01,02,03 into DS? select sum(case when Ticket=’DS01′ OR ‘DSO2’ OR ‘DS03′ THEN customer ELSE 0 END) AS DS sum(case when Ticket=’MSO2’ OR ‘MS03’ THEN customer ELSE 0 END) AS MS from table group by ticket unsure how to …
Syntax error for nested condtionals in SQL
I’m pretty new to redshift and I’ve been trying to to do a nested case when condition here, yet I get a syntax error ERROR: syntax error at or near “)” in this line of SQL: I’m not sure what’s wrong, since I’m familiar with Python and I’m pretty sure this is how…
DAU WAU MAU Error in Redshift: [Amazon](500310) Invalid operation: This type of correlated subquery pattern is not supported due to internal error;
I am trying to compute DAU WAU MAU ratios. DAU: active users on the day WAU: active users of the past 7 days MAU: active users of the past 30 days The DAU:WAU, DAU:MAU, and WAU:MAU ratios compute stickiness of user engagement in terms of %. I have a table called my_table that entails datetime_field user_id Th…
RANK data by value in the column
I’d like to divide the data into separate groups (chunks) based on the value in the column. If the value increase above certain threshold, the value in the “group” should increase by 1. This would be easy to achieve in MySQL, by doing CASE WHEN @val > 30 THEN @row_no + 1 ELSE @row_no END …
Manipulate data in SQL (backfilling, pivoting)
I have a table similar to this small example: I want to manipulate it to this format: Here’s a sample SQL script to create an example input table: CREATE TABLE sample_table ( id INT, hr INT, …