i have a table that looks like this: id position value 5 senior 10000 6 senior 20000 8 senior 30000 9 junior 5000 4 junior 7000 3 junior 10000 It is sorted by position and value (asc) already. I want…
In Postgres, we want to use the window function as an aggregate function. We have a table, where every line consists of two timestamps and a value. We first extend the table by adding a column with the difference between…
I have the first three fields of the following table. I want to compute the number of consecutive days an amount was higher than 0 ("days" field). key date amount days 1 2023-01-23 0 0 1 2023-01-22 10 2 1…
I have a table with an item, its cost and the date it was added. CREATE TABLE item_prices ( item_id INT, item_name VARCHAR(30), item_price DECIMAL(12, 2), created_dttm DATETIME ); INSERT INTO item_prices(item_id, item_name, item_price, created_dttm) VALUES (1, 'spoon', 10.20 ,…
I have a table source_flight_destination like this ID Flight source destination 1 Indigo II BB 2 Air Asia AA II 3 Indigo BB JJ 4 spice jet SS BB 5 Indigo JJ SS 6 Air Asia II KK 7 spice…
I have a quick question in relation to windowing in MySQL SELECT Client, User, Date, Flag, lag(Date) over (partition by Client,User order by Date asc) as last_date, lag(Flag) over (partition by Client,User order by Date asc) as last_flag, case when…
I'm working with the expression builder of Palantir Contour. The following table is given, and I need to fill the gaps in the counter column. Problem: The rows before the first value shall be filled with first counter -1 Date…
I'm fairly new to window functions and have been learning about them recently. There is this query which returns the total sales for each month and quarter using a group by and an aggregate function. Additionally, it returns the total…
I want to perform a fill-down activity in PgSQL DDL: create table brands ( id int, category varchar(20), brand_name varchar(20) ); insert into brands values (1,'chocolates','5-star') ,(2,null,'dairy milk') ,(3,null,'perk') ,(4,null,'eclair') ,(5,'Biscuits','britannia') ,(6,null,'good day') ,(7,null,'boost') ,(8,'shampoo','h&s') ,(9,null,'dove'); Expected output is: category…
How does the code look like to partition the following table. date and status are given, partition column shall be added. Column group is only to explain where the group starts and ends. Finally, I like to do some analytics,…