I've a sample dataset order_num order_date email product_id 101 2023-06-01 [email protected] 1 101 2023-06-01 [email protected] 2 222 2023-06-02 [email protected] 1 333 2023-06-03 [email protected] 3 434 2023-06-05 [email protected] 3 444 2023-06-01 [email protected] 1 444 2023-06-01 [email protected] 2 677 2023-06-02 [email protected] 3…
I have a MYSQL table with this format: Insurance Date Value A 2020-01-01 2 A 2020-02-01 5 A 2020-03-01 4 A 2020-04-01 6 A 2020-05-01 8 A 2020-06-01 1 A 2020-07-01 3 A 2020-08-01 7 A 2020-09-01 9 A 2020-10-01…
I need to fetch records of last date of each month based on the dates of records in the database table. Now, if I fetch the last dates of each month in my table using following query: SELECT MAX(`date`) AS…
I am trying to find the YoY growth of the users and trying to write a mysql query using window functions. Below is my query to find the lag : select substr(create_time, 1, 4) as date, count(*) as count, lag(count(*),…
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'…
Get the average traffic from last week data per WEEK number and get the traffic data for last week Traffic(D-7) For example if date = 5/13/2023, need to output traffic data (Traffic(D-7)) for date = 5/6/2023 I manage to get…
updated I have the following table of updates which you will note contains nulls refer event_date col1 col2 col3 cat 2 today null null bbb a 2 yesterday null null null b 2 yesterday null xyz null b 2 last…
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 need some help. I have a table in PostgreSQL with logs of user sessions. The table has 3 fields: id, action, and action_date in the timestamp format. The action field has 2 possible values: "page_load" or "page_exit". The action_date…