Window function POSTGRESQL
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…
Postgresql substract two columns and substract remainder
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…
How can I calculate a user's daily streak in postgresql?
My goal I'm trying to track and display a user's daily streak posting to my app, but struggling to write a query that works reliably and returns an accurate count. Some context My app has a prompt and a post…
Mysql – SQL: CAST combined with window function causes syntax error
I want to condense my database, which now contains a large number of values, to 1/12 of the original size by averaging twelve consecutive values. To do this, I form the mean value using the types TIMESTAMP and FLOAT(4,2). In…
Postgresql – How to count a records before current record date?
I have a table like this: ----------------- id | date ----------------- 1 | 2023-10-07 2 | 2023-10-08 3 | 2023-10-14 and I want to get the record count up to the current record where the date is less than Sunday…
Postgresql – Distribute Value by Row Percentage in SQL
I have a category, percentage table in SQL (the table itself has been obtained by the following query: SELECT p.category, sum(p.sales) * 100 / sum(sum(p.sales)) OVER () AS percentage FROM product p GROUP BY p.category; Category Percentage Books 20.51% Clocks…
Postgresql – Limit RANGE window function amount of rows considered
I have a PostgreSQL scenario where I have a database table with packet captures (each packet has a timestamp and a length), and I want to calculate the “current” bandwidth in use. I’m currently using a 100 ms sliding window, like…
Postgresql – Using rank() in Postgres
I have the following tables (simplified) in Postgres: CREATE TABLE party( id int PRIMARY KEY, family_name varchar(50) NOT NULL ); CREATE TABLE election( id int, country_name varchar(50) NOT NULL, e_type election_type NOT NULL, e_date date NOT NULL, vote_share numeric, seats…
Why is the sum( ) function needed here in MySQL?
I'm working on an exercise from "Murach's MySQL 3rd Edition" which gives the following prompt: "11. Write a SELECT statement that uses an aggregate window function to calculate a moving average of the sum of invoice totals. Return these columns:…
Mysql – Comparison operators with a CASE statement and Window Function
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…