3 days running average / capco

 data = [Row(Date='2023-01-01', ProductID=100, QuantitySold=10),

        Row(Date='2023-01-02', ProductID=100, QuantitySold=15),

        Row(Date='2023-01-03', ProductID=100, QuantitySold=20),

        Row(Date='2023-01-04', ProductID=100, QuantitySold=25),

        Row(Date='2023-01-05', ProductID=100, QuantitySold=30),

        Row(Date='2023-01-06', ProductID=100, QuantitySold=35),

        Row(Date='2023-01-07', ProductID=100, QuantitySold=40),

        Row(Date='2023-01-08', ProductID=100, QuantitySold=45)]

###########################################################################

I have the following sales data for a product, with the quantity sold on each date:


Date ProductID QuantitySold

2023-01-01 100 10

2023-01-02 100 15

2023-01-03 100 20

2023-01-04 100 25

2023-01-05 100 30

2023-01-06 100 35

2023-01-07 100 40

2023-01-08 100 45

I need to calculate the 3-day running average of the QuantitySold for this product.

use spark/scala/pyspark for calculation

Expected Output

Date ProductID QuantitySold RunningAvg3Days

2023-01-01 100       10 10.00

2023-01-02 100       15 12.50

2023-01-03 100       20 15.00

2023-01-04 100       25 20.00

2023-01-05 100       30 25.00

2023-01-06 100       35 30.00

2023-01-07 100       40 35.00

2023-01-08 100       45 40.00

Comments

Post a Comment

Popular posts from this blog

sql question set 1

✅ SQL Question #1: Repeating Rows Based on Value Input Table : A single-column table with values: Value ----- 1 2 3 Expected Output : Each value should be repeated as many times as its value: Value ----- 1 2 2 3 3 3 Question : Write an SQL query to return each number repeated as many times as its value. For example, 1 should appear once, 2 should appear twice, 3 thrice, and so on. ✅ SQL Question #2: Get the Start and End Cities per Journey Input Table ( TravelRoutes ): Id From To 1 Hyderabad Bangalore 1 Bangalore Chennai 1 Chennai Kochi 2 Bangalore Hyderabad 2 Hyderabad Delhi 3 Delhi Chennai Expected Output : Id From To 1 Hyderabad Kochi 2 Bangalore Delhi 3 Delhi Chennai Question : Write an SQL query to find the starting and ending cities of each journey ( Id ). Assume the path is connected and ordered — you need to find the first From city and the last To city for each Id . 
Read more

SCD Type 2 in MySQL/ coforge

 To implement SCD Type 2 in MySQL , you typically use SQL scripts to: Detect changes between source and target Expire old rows in the dimension table Insert new rows with updated values Assume two tables: customer_src (source/staging table) customer_dim (dimension table with SCD Type 2 tracking) CREATE TABLE customer_dim (     customer_sk INT AUTO_INCREMENT PRIMARY KEY,     customer_id INT,     name VARCHAR(100),     address VARCHAR(255),     start_date DATE,     end_date DATE,     is_current CHAR(1) -- 'Y' or 'N' ); Step 1: Identify Changes (New or Modified Data) -- Find records in source that are new or have changed CREATE TEMPORARY TABLE scd2_changes AS SELECT s.* FROM customer_src s LEFT JOIN customer_dim d   ON s.customer_id = d.customer_id AND d.is_current = 'Y' WHERE d.customer_id IS NULL    OR s.name <> d.name    OR s.address <> d.address; Step 2: Expi...
Read more