How to check records that come one after another in MySQL
I have a table movie that has id, movie name, and status columns. id name status 1 movie1 0 2 movie1 1 3 movie1 0 4 movie1 1 5 movie1 0 6 movie1 0 7 movie1 1 8 movie1 0…
Postgresql – PostgresSQL (AWS Redshift) Forward-fill null values based on previous non-null value in same column
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,…
How to create an SCD2 type table from partitioned table PostgreSQL/Clickhouse?
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)…
Postgresql – Insert missing rows in time series consumption history
I'm looking for assistance with writing a PostgreSQL query in Apache Superset. I have metered consumption data, where the meters have unique IDs. Normally, a meter is read at the start of every hour, and the difference is calculated in…
Mysql – PERL: makes array of item ids shortened
So, I have a large set of numbers, basically 1001 .. 150000 for a database using MySQL There are a ton of gaps in the IDs on the database, so not all IDs exist. It can go from 100000 -…
Postgresql – The problem of extracting consecutive dates in SQL query
There is a table of customers with purchases made in a certain month. It happens that a customer did not buy anything in a certain month, so this month is skipped. Task: I need to extract only those lines where…
Postgresql – Is there any good way to determine streaks of consecutive events in SQL?
I have a Postgres database. It has two tables that we're concerned with right now. Users has a Name field and an ID field, and Events has a UserID field (FK to Users.ID), EventType field (typed as integer), and Time…
Postgresql – How to performantly self-join same table multiple times with ascending and descending sort order?
Data Definition I have a status table like so, attributes not relevant to the question are omitted: id created value 1 2024-06-24T13:01:00 error 2 2024-06-24T13:02:00 ok 3 2024-06-24T13:03:00 warning 4 2024-06-24T13:04:00 error 5 2024-06-24T13:05:00 error 6 2024-06-24T13:05:30 error 7 2024-06-24T13:06:00…
How to find gaps in an int column (grouped by another column) in MySQL?
There are numerous SQL questions and answers about finding gaps in sequences, but I could not find any that have the wrinkle my situation has: I need to group the sequence by another column. My table (simplified) id | parent_id…
Postgresql – Postgres Select Row From Group With Opposite Sort Order
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…