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…
I have two tables in my database as below: categories table: Column Name Column Type ID INT Name TEXT IDParent DOUBLE and advertisements table: Column Name Column Type ID INT IDUser INT DateAdded DATETIME IDCategory INT Price DOUBLE I have…
YEAR sales 2021 1000 2022 1500 2023 2000 I want show 3rd column min sales with window function. But year=2022. YEAR sales min 2021 1000 1500 2022 1500 1500 2023 2000 1500 I've tried the following query, but it didn't…
I have a test CTE as follows: with test_cte (month, company, probability, expected, actual) as (values ('2024-03','CompA',100,4,13), ('2024-03','CompA',80,7,13), ('2024-03','CompA',50,7,13) ) select * from test_cte What I would like to do is add a final column called 'expected_revised' which would be…