1 of 46

Spark Review and Practice Session

1

CS224p

Prepared by Vedant Saraswat

2 of 46

Problem 1 : Grouping Common Event Status Together

Given the following data you are tasked to group the consecutive event states and display the start and end date for each group.

2

Event Date	Event Status
2020-06-01	Won
2020-06-02	Won
2020-06-03	Won
2020-06-04	Lost
2020-06-05	Lost
2020-06-06	Lost
2020-06-07	Won
2020-06-08	Won

3 of 46

Expected Solution

3

Event Date	Event Status
2020-06-01	Won
2020-06-02	Won
2020-06-03	Won
2020-06-04	Lost
2020-06-05	Lost
2020-06-06	Lost
2020-06-07	Won
2020-06-08	Won

Event Status	Start Date	End Date
Won	2020-06-01	2020-06-03
Lost	2020-06-04	2020-06-06
Won	2020-06-07	2020-06-08

4 of 46

PySpark Approach

4

5 of 46

5

6 of 46

6

7 of 46

7

8 of 46

8

9 of 46

SQL Approach

9

10 of 46

10

11 of 46

Problem 2 : Exchange Two Students in Class

Given the following data you are assigned the responsibility of having students exchange data with their alternate counterparts.

11

ID	Student
1	Alice
2	Bob
3	Charlie
4	David
5	Eve

12 of 46

12

ID	Student
1	Alice
2	Bob
3	Charlie
4	David
5	Eve

ID	Student
1	Bob
2	Alice
3	David
4	Charlie
5	Eve

13 of 46

PySpark Approach

13

14 of 46

14

15 of 46

15

16 of 46

16

17 of 46

SQL Approach

17

18 of 46

18

19 of 46

Problem 3 : Number of Calls Between Two Persons

Given the provided data, your task is to group calls between the same individuals, calculating both the total duration and the count of the number of calls.

19

From_id	To_id	Duration
10	20	58
20	10	12
10	30	20
30	40	100
30	40	200
30	40	200
40	30	500

20 of 46

Expected Solution

20

From_id	To_id	Duration
10	20	58
20	10	12
10	30	20
30	40	100
30	40	200
30	40	200
40	30	500

Person 1	Person 2	total_duration	call_count
10	20	70	2
10	30	20	1
30	40	1000	4

21 of 46

PySpark Approach

21

22 of 46

22

23 of 46

23

24 of 46

24

25 of 46

SQL Approach

25

26 of 46

26

27 of 46

Problem 4 : Counting Grand Slam Titles

Given the provided data, your task is to calculate the grand slam titles for each Players

27

Player_id	Player_name
1	Nadal
2	Federer
3	Novak

Year	Wimbledon	Fr_open	US_open	Au_open
2017	2	1	1	2
2018	3	1	3	2
2019	3	1	1	3

28 of 46

Expected Solution

28

Player_id	Player_name
1	Nadal
2	Federer
3	Novak

Year	Wimbledon	Fr_open	US_open	Au_open
2017	2	1	1	2
2018	3	1	3	2
2019	3	1	1	3

Player_id	Player_name	grand_slams
1	Nadal	5
2	Federer	3
3	Novak	4

29 of 46

PySpark Approach

29

30 of 46

30

31 of 46

31

32 of 46

SQL Approach

32

33 of 46

33

34 of 46

Problem 5 : Employees Earning more than managers

Given the provided data, your task is to identify employees who are earning more than managers

34

ID	Name	Salary	ManagerId
1	John	6000	4
2	Kevin	11000	4
3	Bob	8000	5
4	Laura	9000	null
5	Sarah	10000	null

35 of 46

Expected Solution

35

ID	Name	Salary	ManagerId
1	John	6000	4
2	Kevin	11000	4
3	Bob	8000	5
4	Laura	9000	null
5	Sarah	10000	null

Name
Kevin

36 of 46

PySpark Approach

36

37 of 46

37

38 of 46

38

39 of 46

39

40 of 46

Problem 6 : Interview Employees Earning More Than Department Average Salary

Given the provided data, your task is to identify employees who are earning more than Department Average Salary

40

Employee ID	Employee Name	Department	Salary
1	Alice	HR	60000
2	Bob	HR	50000
3	Charlie	Finance	70000
4	David	Finance	75000
5	Eve	Engineering	90000
6	Frank	Engineering	93000
7	Grace	HR	45000
8	Hank	Engineering	98000
9	Ivy	Finance	66000

41 of 46

Expected Solution

41

Employee ID	Employee Name	Department	Salary
1	Alice	HR	60000
2	Bob	HR	50000
3	Charlie	Finance	70000
4	David	Finance	75000
5	Eve	Engineering	90000
6	Frank	Engineering	93000
7	Grace	HR	45000
8	Hank	Engineering	98000
9	Ivy	Finance	66000

Employee Name	Salary	Avg - Salary
Hank	98000	93666
David	75000	70333
Alice	60000	51666

42 of 46

PySpark Approach

42

43 of 46

43

44 of 46

44

45 of 46

SQL Approach

45

46 of 46

46