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…
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…
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…
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…
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…
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…
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:…
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…