I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age. Here's how my tables are defined. users id | age | gender | occupation | zipcode ratings userid…
I am clear on how GROUPING SETS (and ROLLUP and CUBE) work for a single expression. However, I've never entirely understood how it works when multiple GROUPING SETS are combined. Here is an example table that I've created to help…
I have a query that filters the emails of people who "have a quantity" bigger than the median over all quantities found in the table: SELECT AVG(quantity) AS avg_quantity, email FROM table GROUP BY email HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5)…
In my PostgreSQL 14.8 database I have a table named orders that looks as follows: CREATE TABLE orders ( user_id int , order_id int , order_date date , quantity int , revenue float , product text ); INSERT INTO orders…
I have a hierarchal aggregate that uses below statement CREATE MATERIALIZED VIEW IF NOT EXISTS public.values_summary_five_minutes WITH (timescaledb.continuous,timescaledb.materialized_only = true) AS SELECT variableid, time_bucket(INTERVAL ‘5 minute’, bucket_interval_one_min) AS bucket_interval_five_min, MIN(Min_IntValue) as Min_IntValue, MAX(Max_IntValue) as Max_IntValue, SUM(Sum_IntValue) as Sum_IntValue, COUNT(Count_IntValue) as…
I need to write a Query that gives me January 31's rolling 3 day average of total transaction amount processed per day I used the query below, but I'm having problem to retrieve information, because it keeps giving this error:…
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:…
Given a table with a column containing double precision[] with a fixed/same size. I am able to fetch an element wise aggregation for min and max: select min(array_col) from table; select max(array_col) from table; The result should be again a…
this is my student table stud table rollno name 1 A 2 B 3 C 4 B 5 D 6 C this is my marks table marks table rollno mar eng maths phy chem 1 40 45 38 50 50…
I'm trying to figure out a way to reason about a fairly simple problem: A table foo has two columns, id with unique identifiers, and value with a random numeric value. Given a constant threshold value, find the groups of…