---
# System prepended metadata

title: Assignment 2

---

- Daniel Balbach
- Jonas Hofer
- Vinzenz Brantner

# Assignment 2

## Task 1

### 1.1

| Index | Lotterynumber |
|-------|--------|
| 1     | 117    |
| 2     | 224    |
| 3     | 298    |
| 4     | 335    |
| 5     | 215    |
| 6     | 124    |
| 7     | 141    |
| 8     | 127    |
| 9     | 144    |
| 10    | 140    |
| 11    | 135    |
| 12    | 138    |
| 13    | 130    |
| 14    | 138    |
| 15    | 137    |
| 16    | 111    |
| 17    | 124    |
| 18    | 115    |
| 19    | 112    |
| 20    | 135    |
| 21    | 123    |
| 22    | 116    |
| 23    | 105    |
| 24    | 124    |
| 25    | 133    |
| 26    | 104    |
| 27    | 117    |
| 28    | 109    |
| 29    | 125    |
| 30    | 109    |
| 31    | 127    |
| 32    | 99     |
| 33    | 104    |
| 34    | 106    |
| 35    | 136    |
| 36    | 135    |
| 37    | 97     |
| 38    | 129    |
| 39    | 126    |
| 40    | 118    |
| 41    | 108    |
| 42    | 120    |
| 43    | 103    |
| 44    | 117    |
| 45    | 122    |
| 46    | 124    |
| 47    | 81     |
| 48    | 110    |
| 49    | 88     |
| 50    | 106    |
| 51    | 111    |
| 52    | 97     |
| 53    | 102    |
| 54    | 81     |
| 55    | 74     |
| 56    | 89     |
| 57    | 14     |
| 58    | 19     |
| 59    | 15     |
| 60    | 12     |
| 61    | 13     |
| 62    | 16     |
| 63    | 15     |
| 64    | 14     |
| 65    | 12     |
| 66    | 15     |
| 67    | 10     |
| 68    | 19     |
| 69    | 13     |
| 70    | 13     |
| 71    | 13     |
| 72    | 13     |
| 73    | 13     |
| 74    | 17     |
| 75    | 9      |

### 1.2

| number of occurences | Lotterynumber |
| -------------------- | ------------- |
| 335                  | 4             |
| 298                  | 3             |
| 224                  | 2             |
| 215                  | 5             |
| 144                  | 9             |
| 141                  | 7             |
| 140                  | 10            |
| 138                  | 12            |
| 138                  | 14            |
| 137                  | 15            |
| 136                  | 35            |
| 135                  | 20            |
| 135                  | 36            |
| 135                  | 11            |
| 133                  | 25            |
| 130                  | 13            |
| 129                  | 38            |
| 127                  | 31            |
| 127                  | 8             |
| 126                  | 39            |
| 125                  | 29            |
| 124                  | 46            |
| 124                  | 6             |
| 124                  | 17            |
| 124                  | 24            |
| 123                  | 21            |
| 122                  | 45            |
| 120                  | 42            |
| 118                  | 40            |
| 117                  | 1             |
| 117                  | 44            |
| 117                  | 27            |
| 116                  | 22            |
| 115                  | 18            |
| 112                  | 19            |
| 111                  | 16            |
| 111                  | 51            |
| 110                  | 48            |
| 109                  | 28            |
| 109                  | 30            |
| 108                  | 41            |
| 106                  | 34            |
| 106                  | 50            |
| 105                  | 23            |
| 104                  | 33            |
| 104                  | 26            |
| 103                  | 43            |
| 102                  | 53            |
| 99                   | 32            |
| 97                   | 52            |
| 97                   | 37            |
| 89                   | 56            |
| 88                   | 49            |
| 81                   | 47            |
| 81                   | 54            |
| 74                   | 55            |
| 19                   | 68            |
| 19                   | 58            |
| 17                   | 74            |
| 16                   | 62            |
| 15                   | 66            |
| 15                   | 59            |
| 15                   | 63            |
| 14                   | 57            |
| 14                   | 64            |
| 13                   | 61            |
| 13                   | 69            |
| 13                   | 70            |
| 13                   | 71            |
| 13                   | 72            |
| 13                   | 73            |
| 12                   | 65            |
| 12                   | 60            |
| 10                   | 67            |
| 9                    | 75            |


## Task 2

### 2.1

| Step | Explanation |
|------|-------------|
|Map|read key/value pairs and transform them into new intermediate key/value pairs |
|Combine | reduces number of key/value pairs by aggregating the previously created pairs. --> reduce data on map node --> reduce task can be operated on less data |
|Partition| send intermediate key to reduce task |
|Shuffle | In final output of map, there can be multiple partitions. Trasnfer map output partitions to the reduce tasks. --> copy input for reduce task to local file system |
| Sort | sort the data (=key-value pairs) based on the keys|
| Reduce | After getting data from map task --> aggregate key-value pairs with same key/value pairs --> "count" |

### 2.2

| dropoff_date | distance | Earnings |
| -------- | -------- | -------- |
|2015-06-01|137223.42999999973|1409103.739999991|
|2015-06-02|131085.86999999988|1366296.2500000065|
|2015-06-03|139624.7500000004|1378500.4699999925|
|2015-06-04|152275.7299999987|1472577.3499999992|
|2015-06-05|180111.99999999965|1741694.440000008|
|2015-06-06|220171.5000000023|2005130.620000007|
|2015-06-07|208587.1599999983|1795657.6600000008|
|2015-06-08|3212.6099999999983|16144.989999999993|



### 2.3

| Date | Gross Margin (Buffalo) |
| -------- | -------- |
|2015-06-01|0.9873355752256012|
|2015-06-02|0.9877031451134337|
|2015-06-03|0.9864950112542701|
|2015-06-04|0.9866977981590237|
|2015-06-05|0.9864059422296366|
|2015-06-06|0.9856172135594049|
|2015-06-07|0.985002560490205|

In the fuel dataset there is no Entry for the 8th of june 2015. This is why for this day it's not possible to calculate the gross margin.



## Task 3
### 3.1

Query:

``` SQL
SELECT num, count(num) as count FROM (
SELECT split(l.lotterynumbers, ' ')[0] as num FROM lottery l
UNION ALL
SELECT split(l.lotterynumbers, ' ')[1] as num FROM lottery l
UNION ALL
SELECT split(l.lotterynumbers, ' ')[2] as num FROM lottery l
UNION ALL
SELECT split(l.lotterynumbers, ' ')[3] as num FROM lottery l
UNION ALL
SELECT split(l.lotterynumbers, ' ')[4] as num FROM lottery l
) unionresult
GROUP BY num
ORDER BY count DESC
LIMIT 5
```
Result:

|index|num|count|
|--|---|-----|
|1|02 |117  |
|2|35 |113  |
|3|20 |113  |
|4|25 |112  |
|5|14 |111  |


### 3.2
Query:

```sql
SELECT  t.dropoff_date as dropoff_date,
        t.distance as distance, 
        t.earnings as earnings,
        (t.earnings - t.distance/21.3*f.buffalo_price)/t.earnings as gross_margin 
FROM
    (SELECT t.dropoff_date AS dropoff_date, 
            SUM(t.earnings) as earnings, 
            SUM(t.distance) as distance
     FROM taxi as t
     GROUP BY dropoff_date) as t
     JOIN fuel f
     ON (t.dropoff_date = f.businessday)

```

Result:

| dropoff_date | distance  | earnings   | gross_margin   |
| ------------ | --------- | ---------- | -------------- |
| 2015-06-01   | 137223.43 | 1409103.74 | 0.987335575226 |
| 2015-06-02   | 131085.87 | 1366296.25 | 0.987703145113 |
| 2015-06-03   | 139624.75 | 1378500.47 | 0.986495011254 |
| 2015-06-04   | 152275.73 | 1472577.35 | 0.986697798159 |
| 2015-06-05   | 180112.0  | 1741694.44 | 0.98640594223  |
| 2015-06-06   | 220171.5  | 2005130.62 | 0.985617213559 |
| 2015-06-07   | 208587.16 | 1795657.66 | 0.98500256049  |


