I'm doing this StrataScratch SQL question. The suggested solution uses two subqueries for each max value of each department, and then it substracts them to get the absolute value. I wanted to solve this question using dense_rank/rank window function by…
I have a table: create table accounts_service.operation_history ( history_id bigint generated always as identity primary key, operation_id varchar(36) not null unique, operation_type varchar(30) not null, operation_time timestamp with time zone default now() not null, from_phone varchar(20), user_id varchar(21), -- and…
I'm working with PostgreSQL and using pg_export_snapshot() to create a snapshot in one transaction, which I then import into another transaction using SET TRANSACTION SNAPSHOT. I noticed that PostgreSQL requires the original (exporting) transaction to stay active while the snapshot…
I wish to achieve something like this from a MYSQL SELECT query I wish to group the results from the select in 500 rows (per batch) and have a column called group (or anything) for the key to the group…
Imagine a table of user messages that looks like this: | ID | MESSAGE | RECIPIENT_ID | ULTIMATE_PARENT_ID | SENT_AT | | 1 | Blah. | 10 | 1 | 2024-09-10T10:10:00 | | 2 | Blah2 | 10 | 2…
I'm a noob in the recursive domain in that I understand the syntax but I'm unable to apply it to my current problem. I'm using Postgre SQL. I have a product_id 'ABC123' that has a parent category 'FG_ACC_CA', which itself…
I have a table with student data which includes their start and withdrawal dates. Not all students have withdrawal dates as they are still enrolled. I am trying to calculate the number of new students per month, withdrawals per month,…
From "Montreal" as the origin point, I'm trying to find the shortest path to go to another city. But I'm having difficulty when a city is looped. demo at db<>fiddle create table flights(id,src,dst,cost)as values (0,'Montreal','NY', 742.94) ,(1,'Montreal','Detroit', 362.47) ,(2,'NY', 'Detroit',…
I have a sql as shown below. SELECT * FROM payment WHERE is_deleted = 0 AND is_privacy = 0 AND ou_code IN ('A1', 'A2', ..., 'W1', 'W2'...) LIMIT 0, 20 There are many elements after in, about 3,000.payment table has…
I have a table zipnames with four columns: id, zipcode, name_fi and name_sv. Both of the name_* columns are nullable. For each possible zipcode, I might have up to four rows: both names present, only name_fi present, only name_sv present,…