I have 2 tables on my db: 1- scheduled services 2- services realized. On 2nd table, I have the value $ received for the service and the date the service was realized. On 1st table, I only have the scheduled…
I have the following table: CREATE TABLE IF NOT EXISTS test ( date DATE, value INT ); INSERT INTO test VALUES ( '2022-01-11', NULL), -- Tues ( '2022-01-10', 5), -- Mon ( '2022-01-09', NULL), -- Sun ( '2022-01-08', 6), --…
I was asked this question in a job interview: There is a table with vehicle names mentioned in a column. Output when we check for name=car we must get as 4 i.e the maximum count of continuous occurrence of 'car'…
I have many records in the table. I was grouped by continuous date. example : begin_at (From Date) end_at (to date) 2023-05-05 00:00:00 2023-06-18 00:00:00 2023-06-11 00:00:00 2023-06-18 00:00:00 2023-06-12 00:00:00 2023-06-18 00:00:00 2023-06-13 00:00:00 2023-06-18 00:00:00 2023-06-14 00:00:00 2023-06-18…
I have a table: user_id type date 1 prime 2022-05-31 1 usual 2022-06-30 1 usual 2022-07-31 1 usual 2022-09-31 I need to get a table grouped by user_id and type and validity period. The main difficulty is that the periods…
I have the following table: CREATE TABLE trajectory( user_id int, session_id int, timestamp timestamp with time zone, lat double precision, lon double precision ); INSERT INTO trajectory(user_id, session_id, timestamp, lat, lon) VALUES (1, 25304,'2008-10-23 02:53:04+01', 39.984702, 116.318417), (1, 25304, '2008-10-23…
I have the following sample data with 3 columns, i.e., account_id, interval_start, interval_end. And I want to create a reactivated column with the given output in the following table. For each account_id, reactivated column should start with a 0 value…
I have a table of changes like this CREATE TABLE IF NOT EXISTS changes ( entity_id TEXT NOT NULL, column_id TEXT NOT NULL, value JSONB NOT NULL, updated_at TIMESTAMP NOT NULL ); INSERT INTO changes VALUES ('1', 'height', to_jsonb(140), '01-01-2021…
create table hall_events ( hall_id integer, start_date date, end_date date ); delete from hall_events; insert into hall_events values (1,'2023-01-13','2023-01-14') ,(1,'2023-01-14','2023-01-17') ,(1,'2023-01-15','2023-01-17') ,(1,'2023-01-18','2023-01-25') ,(2,'2022-12-09','2022-12-23') ,(2,'2022-12-13','2022-12-17') ,(3,'2022-12-01','2023-01-30'); select * from hall_events; This is my table **my output shoud be ** hall_id start_date…
I am trying to add another column called session_id. I want to rank according to the condition that if the time difference between the date_time is more than 30 minutes, then that will be counted as another session. Here is…