I'm trying to use a case function with a window function, but the data I'm getting doesn't add up. Everything seems to work, but I'm only receiving "Above Average" for that entire CASE function, so I know something isn't quite…
This is my sample dataset. Seq_no and value columns are given, Cum.sum and Bucket columns we need to find using postgresql. To Find cumulative sum , i can write sum(value) over (order by seq_no rows between unbounded preceding and current…
i have a table like this: name used time asd 10 15:00 bsf 15 15:00 asd 20 14:55 bsf 0 14:55 i need to make a query that returns values like this: the result i need for the grafana timeseries…
I'm not able to quite figure out how to capture the changes over time. I have the following: db<>fiddle CREATE TABLE term_changes (load_id int, merchant_id int, load_date date, terms varchar(50)); INSERT INTO term_changes (load_id, merchant_id, load_date, terms) VALUES (1, 1,…
From the following table of sport matches: id_ p1_id p2_id match_date p1_stat p2_stat 852666 1 2 01/01/1997 1301 249 852842 1 2 13/01/1997 2837 2441 853471 2 1 05/05/1997 1474 952 4760 2 1 25/05/1998 1190 1486 6713 2 1…
I have 2 tables on my db: 1- scheduled services 2- services realized. On 2nd table, I have the value $ received for the service and the date the service was realized. On 1st table, I only have the scheduled…
So basically I have a table where are partners and the dates when then they did their orders. partner_id order_date 1 2022-01-02 1 2022-01-20 2 2022-02-20 4 2022-01-15 4 2022-01-17 4 2022-01-30 And I want to have an information of…
I have the following table: CREATE TABLE IF NOT EXISTS test ( date DATE, value INT ); INSERT INTO test VALUES ( '2022-01-11', NULL), -- Tues ( '2022-01-10', 5), -- Mon ( '2022-01-09', NULL), -- Sun ( '2022-01-08', 6), --…
I would like to make a query similar to this: SELECT time, close - LAG(close) OVER (ORDER BY time) AS "diff" CASE WHEN diff > 0 THEN diff ELSE 0 END AS gain, CASE WHEN diff < 0 THEN diff…
Let's assume there is a table: day amount 1 10 2 20 3 30 I want to know when turnover reached 30, in case above it's happened on 2 day. How to query this in SQL? I guess I have…