I want to get moving sum and moving average on each date for last 7 days (including current day). I used WINDOW function and used ROWS BETWEEN to frame the function which calculates correctly, but it shows sum and avg…
I have the following data in a PostgreSQL table: id amount opening_amount closing_amount 1 200 1000 1200 2 -500 NULL NULL 3 -200 NULL NULL 4 1000 NULL NULL I want to convert this data into a ledger format, calculating…
I have the following dataset: create schema m; create table m.parent_child_lvl_1(customer_id,date,order_type,order_id,sub_id) as values (108384372,'18/09/2023'::date,'sub_parent_first_order',5068371361861,407284605) ,(108384372, '13/11/2023', 'sub_order', 5134167539781, null) ,(108384372, '8/01/2024', 'sub_order', 5214687526981, null) ,(108384372, '4/03/2024', 'sub_order', 5283166126149, null) ,(108384372, '18/06/2024', 'sub_parent_order', 5421811138629, 500649255) ,(108384372, '12/08/2024', 'sub_order', 5508433641541, null) ,(108384372,…
I have a table partitioned by date-type column ds, with many fields. Not all columns change every day, therefore most rows are just duplicates of the previous rows. I want to create an SCD2 table from the existing one (partitioned)…
I need to create an id-column: on every TRUE statement it should be a normal serial number, but on every FALSE statement the field value should contain the previous number in this column. In other words, every FALSE should repeat…
I am using AWS Redshift. I have a table that looks like this: Id key value1 value2 value3 1 xxx A NULL NULL 1 xxx NULL B NULL 2 uuu NULL NULL C If the id is repeating, the entire…
I am working on a transaction dataset with one date column & one identifier column. Now retroactively I want to create a boolean column to check whether any particular activity (e.g. marketing campaign) should have been sent for that identifier…
I am very new to PHP and SQL and building a project for hockey statistics. For this page, I am looking to create a SQL query to rank hockey leaders based on goals scored. I am trying to take a…
I want to write a Postgres function that looks up the value of a column in a given table. Example setup: CREATE SCHEMA IF NOT EXISTS example; CREATE TABLE example.price_table ( symbol text NOT NULL, time_of timestamptz NOT NULL, price…
I have a Postgres table foobar with the following schema: id: integer group: integer foo: integer bar: integer timestamp: integer I keep track of updates to various groups, and those updates mutate the foo and bar properties. Every time I…