I'm fairly new to window functions and have been learning about them recently. There is this query which returns the total sales for each month and quarter using a group by and an aggregate function. Additionally, it returns the total…
I want to perform a fill-down activity in PgSQL DDL: create table brands ( id int, category varchar(20), brand_name varchar(20) ); insert into brands values (1,'chocolates','5-star') ,(2,null,'dairy milk') ,(3,null,'perk') ,(4,null,'eclair') ,(5,'Biscuits','britannia') ,(6,null,'good day') ,(7,null,'boost') ,(8,'shampoo','h&s') ,(9,null,'dove'); Expected output is: category…
How does the code look like to partition the following table. date and status are given, partition column shall be added. Column group is only to explain where the group starts and ends. Finally, I like to do some analytics,…
I have two tables Account table id | account_no ----------------------- 1 | 111 2 | 222 Account details id | act_id (fk) | amount | created_dt_ | created_by ------------------------------------------------ 1 | 1 | 10 | 2022-10-30 | SYSTEM 2 |…
I have a table with payment history payments: id consumer_id amount created_at 1 1 30 2021-05-11 13:01:36 2 1 -10 2021-05-12 14:01:36 3 1 -2.50 2021-05-13 13:01:36 4 1 -4.50 2021-05-14 13:01:36 5 1 20 2021-05-15 13:01:36 In final result…
I have a repayment table that looks like this below, cus_id due_date principal disbursed_date 1 01-01-2022 10 01-11-2021 1 01-02-2022 10 01-11-2021 1 01-03-2022 10 01-11-2021 2 15-03-2022 20 15-02-2022 1 01-04-2022 10 01-11-2021 3 01-04-2022 15 20-03-2022 2 15-04-2022…
I have a table Table name - commands id name status group_id id - number name - string status - 0 or 1 group_id - number I need to sort as follows: for all elements with the same group_id I…
I am trying to create a cumulative count for unique customers only by the month they purchased. The example Table is: customer_email cohortMonth [email protected] 10/2019 [email protected] 10/2019 [email protected] 10/2019 [email protected] 11/2019 [email protected] 11/2019 [email protected] 12/2019 The output I am looking…
I have this SQL query: SELECT users.id, users.name, users.avatar, MAX(messages.created_at) max_created_at, MAX(messages.body) FILTER (WHERE messages.created_at = MAX(messages.created_at)) last_message, CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) = 0) THEN true ELSE false END is_read, COUNT(messages.is_read) FILTER (WHERE…
I have a query that orders the result by a specific none-unique column. Now I want to get the first N rows after a specific ID (the primary key). I have a table similar to this: ID Size 0 3…