The following SQL query creates a table named gym with the f…
The following SQL query creates a table named gym with the following columns and data: DROP TABLE IF EXISTS gym;CREATE TABLE gym (trans_id int PRIMARY KEY, userid text, workout_type text, calories_burned int, checkin timestamp, duration int);INSERT INTO gym VALUES(1,’user_1063′,’CrossFit’,429,’2023-06-01 07:06:00′,38),(2,’user_1104′,’Swimming’,954,’2023-06-01 10:54:00′,67),(3,’user_1104′,’CrossFit’,1464,’2023-06-01 18:52:00′,140),(4,’user_1071′,’CrossFit’,1325,’2023-06-02 11:50:00′,61),(5,’user_1071′,’Weightlifting’,344,’2023-06-03 06:24:00′,127),(6,’user_1063′,’Yoga’,344,’2023-06-03 12:06:00′,48),(7,’user_1104′,’Swimming’,1102,’2023-06-03 14:29:00′,112),(8,’user_1023′,’Yoga’,849,’2023-06-03 17:14:00′,133),(9,’user_1023′,’CrossFit’,723,’2023-06-03 19:02:00′,139),(10,’user_1063′,’Cardio’,1028,’2023-06-04 16:27:00′,122),(11,’user_1071′,’Pilates’,698,’2023-06-04 19:15:00′,128),(12,’user_1071′,’Yoga’,672,’2023-06-05 08:58:00′,168),(13,’user_1104′,’Weightlifting’,291,’2023-06-05 09:13:00′,122),(14,’user_1023′,’Weightlifting’,1682,’2023-06-05 11:00:00′,170),(15,’user_1071′,’Weightlifting’,432,’2023-06-06 06:20:00′,177),(16,’user_1071′,’CrossFit’,948,’2023-06-06 11:48:00′,55),(17,’user_1104′,’Yoga’,805,’2023-06-06 15:09:00′,158),(18,’user_1104′,’Yoga’,998,’2023-06-07 08:12:00′,151),(19,’user_1071′,’Swimming’,502,’2023-06-07 08:56:00′,171),(20,’user_1063′,’Cardio’,1058,’2023-06-07 09:28:00′,65),(21,’user_1023′,’Yoga’,1169,’2023-06-07 14:26:00′,84),(22,’user_1071′,’Weightlifting’,1012,’2023-06-08 06:02:00′,157),(23,’user_1104′,’Yoga’,502,’2023-06-08 16:29:00′,75),(24,’user_1071′,’Weightlifting’,1194,’2023-06-09 07:07:00′,159),(25,’user_1063′,’Yoga’,322,’2023-06-11 09:48:00′,113),(26,’user_1063′,’CrossFit’,1387,’2023-06-11 13:03:00′,179),(27,’user_1104′,’CrossFit’,637,’2023-06-14 13:45:00′,146) ; Source: https://www.kaggle.com/datasets/mexwell/gym-check-ins-and-user-metadataLinks to an external site. Here are brief descriptions of the data fields: trans_id: unique identifier for the visit userid: ID of the user who checked in workout_type: Type of workout performed during the visit calories_burned: Estimated number of calories burned during the workout checkin: date and time user checked in duration: time from check in to completion of workout (minutes) Using pgAdmin, execute the table creation script provided above to initialize your dataset. Then, construct a SQL query that accomplishes the following tasks using a Common Table Expression (CTE) structure, organized into three logical parts: Part 1: Daily Aggregation by User Each user may check in multiple times per day. Your first step is to extract the date component from the checkin timestamp and alias it as checkin_date. Then, aggregate the data at the (userid, checkin_date) level to compute: cal_per_min: Represents the daily rate of calories burned per minute for each userid, computed by dividing the total daily calories_burned by the corresponding total daily duration. This metric quantifies individual workout intensity by measuring the average calories burned per minute for each user. Your output should include the following four columns: userid, checkin_date, and cal_per_min. –> Example: On 2023-06-06, user user_1071 burned about 5.95 calories per minute; On 2023-06-11, user user_1063 burned about 5.85 calories per minute. Part 2: Moving and Overall Averages Extend the CTE from Part 1 to compute the following metrics: Part 2.1: 4-Day Moving Average (Excluding Current Day) For each user and check-in date, calculate a 4-day moving average of cal_per_min, using the current date and the three most recent preceding check-in dates. The window should exclude any future dates beyond the current check-in date. Name this column cal_4dma. Additionally, count the number of days included in each moving average window and store this as num_days. Part 2.2: Overall Average In the same CTE, use a different window frame to compute the overall average of cal_per_min for each user across all available check-in dates. Name this column cal_avg. Part 3: Final Output Return the following columns: userid, checkin_date, cal_per_min, cal_4dma, cal_avg Filter the results to include only those rows where the 4-day moving average (cal_4dma) is based on a full window of four transaction days. The final result set should align with the structure and layout of the sample output shown below, with the exception of minor rounding differences. userid checkin_date cal_per_min cal_4dma cal_avg user_1063 2023-06-11 5.85 10.79 9.80 user_1071 2023-06-06 5.95 8.47 7.09 user_1071 2023-06-07 2.94 4.53 7.09 user_1071 2023-06-08 6.45 4.58 7.09 user_1071 2023-06-09 7.51 4.83 7.09 user_1104 2023-06-07 6.61 7.25 6.67 user_1104 2023-06-08 6.69 5.98 6.67 user_1104 2023-06-14 4.36 5.20 6.67 Here is a template to follow for constructing the query:– Use common table expression to write the query in three partsWITH daily_workout AS ( –Part 1 ),moving_average AS ( –Part 2) — Part 3SELECT Submit your complete query in the window below.
Read Details