I have the table contacts which contains duplicate records: id name is_contacted created_at I need to delete duplicates, but keep the first record(among the duplicates for each name) where is_contacted=1. If among the record duplicates there are no records where…
I am having a table marks, I need to print the details of names having continuously increasing marks. Table : Marks Name Sem Marks A 1 80 A 2 90 A 3 95 B 1 80 B 2 70 B…
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…