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, …

Find missing entries by date

I have two redshift tables: alert_type: where i keep types of alerts in my system alert: where i keep alerts Every day I generate a new alert for each alert type. If something has failed in …

How to get data for past X weeks in redshift?

I have a below query which I run gives me the single count for previous week which is Week 44. Current week is 45 as of now. with data_holder as ( with tree_post as (Select contractid as conid, max(…

How to join tables when left join does not generate null values for the dates

I have three tables as shown below: 1. kit_test tk tk.id tk.first_name tk.last_name tk.dob tk.registered_dt SD007 Aarushi Sharma 2/23/1987 10/5/2020 SD008 Camden Howard …

SQL – Use dense_rank and group by together

I was under the impression that when we use rank/row_number/dense_rank, we can NOT use group by, but why does below logic run successfully Select product, type, dense_rank() over (partition by type …

Case when column a from tableX is present in column b of tableY

Below is my use case, i’m querying redshift tables, using case when but get error in case when statement. ERROR: Statement 2 is not valid. ERROR: syntax error at or near “b” MY SQL query: CREATE …

Create Redshift table with new Geometry type through psycopg2

After Redshift announced support for Geometry types and spatial functions, I’d like to create a table with polygons for all countries. I’m failing to do the INSERT and would appreciate help. Here is …

Extract date part from timestamptz

I am trying to extract the hour from a timestamp with a timezone. However, my times are coming up incorrectly. Here’s an example, I am using Dbeaver with my timezone set to EST: SELECT ‘2020-01-24 …

Redshift Rounding Off Issue

I have a table which has a numeric(23,2) field that I need to divide to a constant. My baseline is this aggregation select site, sum(sales) / 1.07 as sales from sales group by site; But when I add …

Redshift: Find max level of previous mode

I have a user table that store level of each category completion. User can unlock the second and the third category after a few levels of the first category completion. My goal is to find which level …