I have the following code in PostgreSQL, which I need to reproduce on Amazon Redshift: select * from ( select distinct a.id , sum(case when a.is_batch_empty then 1 else 0 end) over (partition by a.client_id order by a.id) as empty_count…
The table is represented by following scripts: CREATE TABLE sales ( id SERIAL PRIMARY KEY, product_id INTEGER, sales_date DATE, quantity INTEGER, price NUMERIC ); INSERT INTO sales (product_id, sales_date, quantity, price) VALUES (1, '2023-01-01', 10, 10.00), (1, '2023-01-02', 12, 12.00),…
I have very simple table: create table tmp( id int, source_id int, event_user varchar, event_date timestamp ); and here is the data: insert into tmp(id, source_id, event_user, event_date) values (1, 1, 'A', now()), (2, 1, 'A', now()+interval '1 day'), (3,…
I am trying to get the ids of the customers who have placed orders on consecutive days. The table is created as follows: create table orders( orderid INT, orderdate date, customerid int ); The values: insert into orders (orderid, orderdate,…
I have a table that has daily data and thousands of rows per day: create table my_table(visitors_count int, dt date); select setseed(.42); insert into my_table select (random()*100)::int, current_date+(-random()*14)::int from generate_series(1,42000); However, some days are not present due to holidays: delete…
My task: The variables x and def are a counter and a boolean respectively. Both start at zero. If at any point in time x > 2 then def = 1 from that point onward. Now if def = 1…
I have two tables: dates: date 2024-01-01 2024-01-02 2024-01-03 2024-01-04 2024-01-05 actual: business_date id 2024-01-01 1 2024-01-02 2 2024-01-04 4 2024-01-05 5 I want to join these tables on the condition date = business_date, but in the case where there's…
Apparently, sorting is somehow applied incorrectly in mysql 8 (checked on 8.0.33 to 8.0.35) when query has ORDER BY + GROUP BY + GROUP_CONCAT() + COUNT(*) OVER() window function. See test case below (NOTE that it's synthetic and oversimplified for…
Let's assume I have a table with 3 columns: Id, Value, date Time. I need to find the min, max, first and last value of each 1 minute window between the provided start and end time. And in case no…
I am practising SQL on leetcode (Using MySQL) and came across the following problem - where I am confused on why my solution does not work. The following is an employee table where it shows which department(s) an employee is…