We will creаte а simulаtiоn оf the dealer fоr the Poker card game and compute the probabilities of each possible hand. A list of poker winning hands from ChatGPT appears below: Note that I have crossed out Royal Flush and Straight Flush because their probabilities are very low; they can be counted under standard "flush", even if in sequence. You will begin by downloading an Excel XLSX file containing a template spreadsheet for this exam question at the link below: Link to XLSX template The template's spreadsheet called "Poker Sims" contains 1000 randomized hands, each consisting of 5 cards from the card deck, in columns F:J (do not worry, for I used formulas to generate them instead of writing them by hand). Do not change anything in the "Aux" spreadsheet or in columns F:J of "Poker Sims"; all your work should be contained in the other columns of the "Poker Sims" spreadsheet. To compute the game for each hand, you will fill the cells K8:AU1007 with appropriate formulas as detailed below. Once you complete this work, you will compute the probabilities of each possible hand based on the values in columns AV:BC. Your work will proceed and be graded as follows: 1. (10 points) In the cells K8:O1007, you will use the cards shown in columns F:J and the lookup table in columns A:C to translate each card into a numerical ID for the card value. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 2. (10 points) In the cells P8:T1007, you will use the cards shown in columns F:J and the lookup table in columns A:C to obtain a numerical ID for the suit of each card. As evident in the table, spades have ID 1, diamonds have ID 2, clubs have ID 3, and hearts have ID 4. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 3. (10 points) In the cells U8:AG1007, you will compute counts of how many cards in the hand have a given value, from 1 (ace) to 13 (king). These counts will allow you to figure out the game in the hand later. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. [Hint: you can use the header cells in row 8 in your formulas!] 4. (5 points) In cells AH8:AH1007, you will implement a check that all cards in the hand have been counted correctly in the previous 13 columns. 5. (10 points) In the cells AI8:AL1007, you will compute counts of how many cards in the hand belong to a given suit, from 1 (spades) to 4 (hearts). These counts will allow you to figure out the game in the hand later. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. [Hint: you can again use the header cells in row 8 in your formulas!] 6. (5 points) In cells AM8:AM1007, you will implement a check that all cards in the hand have been counted correctly in the previous 4 columns. 7. (32 points) In the cells AN8:AU1007, you will implement the poker game "logic" to identify the kind of poker hand obtained. These cells should contain "Y" for the column corresponding to the hand obtained and "N" for the columns corresponding to all other hands. Thus, there should only be one "Y" and eight "N" in each row. You should be able to identify the hand based on the counts for each card value and suit computed earlier. You should be careful to flag only the best hand for the cards given; for example, a "full house" should not be counted as "3 of a kind" or "2 of a kind". You should write eight distinct formulas, one per winning hand/column, and copy those formulas to all 1000 rows. Each correct identification is worth 4 points. 8. (8 points) In the cells AV8:BC8, you will compute the probabilities of the eight possible hands. That is, count how many trials scored each possible card total/number of cards and divide those values by the total number of trials. Show probabilities as percentages with one decimal. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 9. (10 points) Use the table from the previous part to generate a chart illustrating these probabilities. Your charts should follow the Excel coding specifications given in the class. You should reuse existing spreadsheet cells as chart features (e.g., title and labels) whenever possible, but you do not need to create new cells otherwise. Make the two charts appear in their own sheets in the workbook. For parts 1-3, 5, and 8, copying cells may change some cell's borders; you do not need to "fix" these, as they do serve as indicators of using formula replication as required by the problem.
The fоllоwing SQL query creаtes а tаble named gym with the fоllowing 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.
Fоr the functiоn f, cоnstruct аnd simplify the difference quotient .f(x) = 3x2 + 2x