Mysql – Count certain values between other values in column
I have the following very simplified situation in a table: datetime id event -------- -- ----- dt01 67 1 dt02 67 2 dt03 106 1 dt04 106 4 dt05 106 2 dt06 654 1 dt07 654 4 dt08 654 3…
Why do the CTEs work different from the Subqueries in MySQL?
I try deleting all the goods, whose type_name starts with D. Let's say I wanna delete goods from the types of dentistry, delicacies, darts_game. So I check whether the type is in the list d_goods_ids (type is a FK of…
Postgresql – Calculate a set of rows with from_date and to_date based on a single date column
I have an customer_audit table, that logs INSERT and UPDATE happened on a customer table. customer_audit id operation timestamp customer_id address1 address2 1 I 2024-10-05 100 link st number 1 2 U 2024-10-06 100 link st number 2 3 U…
Postgresql – How to query cases with multiple pattern matching values in the same column
Ex Table: Name Location John Smith NewYork1131413431 John Smith NewYork2222213 Marc White Boston111118174124712 Sarah Phillips NewYork2222123 Sarah Phillips Boston2222918 I'm trying to query only the names in which they exist in 2 or more cities (for the case of this…
SELECT FOR UPDATE SKIP LOCKED in Postgresql is not skipping a record that should be skipped
I would like to build a queue of Articles where each article has a last_seen_at. User arrives on web page, server ORDERs Articles by last_seen_at ASC, selects the first and returns to the browser. The server updates last_seen_at to be…
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,…
Postgresql – How can I select rows from a table where the sum of rows in a joined table is greater than a number?
I have a order table and a payment table where multiple payments are joined to an order. order id name 1 order 1 2 order 2 payment id order_id amount 1 1 2000 2 1 3000 3 2 500 4…
Postgresql – How to allow mixed integer and numeric parameters in icase
Creatinf icase function using CREATE OR REPLACE FUNCTION public.icase( cond1 boolean, res1 anyelement, cond2 boolean, res2 anyelement, conddefault anyelement) RETURNS anyelement LANGUAGE 'sql' IMMUTABLE PARALLEL UNSAFE AS $BODY$ SELECT CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5…
Mysql – Get count of id for each day and Group by date
Currently, I have a query that looks like this: SELECT send_id, count(id) FROM `table1` WHERE date BETWEEN '2024-09-01%' AND '2024-09-30%' GROUP BY send_id; This returns a result that looks like this: | send_id | count(id) | ------------------------------- | 00123 |…
Postgresql – Postgres split time ranges into 1 minute slots, without rounding boundaries
I have a postgres table with timestamp columns: start_datetime end_datetime duration id 2021-10-17 03:13:00 2021-10-17 03:15:02 302 6214550 2021-10-17 03:15:02 2021-10-17 03:17:03 4,021 6214551 which i need to split out in to buckets constrained to either the end of the…