SQL Candy Assignment - Paper Answers

PLEASE READ ATTACHMENT FIRST FOR DETAILED INSTRUCTIONS AND SCREENSHOTS.

The details for this assignment are attached. Here are the basics:

Attachments:

· 2017_product_data_students-final.csv

· 2018_product_data_students-final.csv

· 2019_product_data_students-final.csv

· Candy_part_1_skeleton_for_students.SQL

Your company wants to merge its old product order data into a new data mart to facilitate analysis. You have been tasked with writing an ETL (extract, transform, and load) code sequence, and executing it on three years’ worth of order data.

In this assignment, you will produce SQL code which scrubs and imports each of the three years’ worth of data, and produces an output file called stagingTable.

Along with these instructions, there is another document, ‘Additional Clarification on the Week 6 Candy Assignment’. Please read that document carefully.

You should also read the ‘Data Notes’ in part 3 of this document. It is very important that you understand the data and how the data changes over the three years, so you can create a ‘stagingTable’ the effectively combines the data that might have been captured in different ways over the years.

Let’s get started!

Part A: Upload all the files you will need to SQLlite:

1. Import the file called “2017_product_data_students.csv” to SQLiteonline.com. When you import it, give it the table name “pd2017” (no quotes) and set the column name to “First line.”

2. Import “2018_product_data_students.csv” as “pd2018”

3. Import “2019_product_data_students.csv” as “pd2019”

4. If you SELECT * FROM pd2017, you should see something like the below screenshot. Note you should see all three of the import tables on the left, and the pd2017 data should match what is shown as selected.

Part B: Extract and Transform your data

Your job is to use SQL to perform an ETL which will accomplish the following: INSTRUCTIONS IN ATTACHMENT.

1. Start with the skeleton starter script we give you, attached to this assignment. Modify the CREATE TABLE command so the schema is as follows: SEE ATTACHMENT

2. Get the 2017 bit of the script working. SEE ATTACHMENT

3. Get the 2018 part of the script working. SEE ATTACHMENT

4. Get the 2019 part of the script working. SEE ATTACHMENT

5. The script will load it into one final table and call it stagingTable

6. Run the checksum script to verify you have the stagingTable calculated correctly.

7. Export your final output table under the name “XX_output_final.csv” where XX are your initials. To export this, you can just use the Export button on the SQLlite menu (it’s right next to the Import button.)

You should do this all in SQLlite. You should not export to Excel and do your manipulations in Excel.

Part C: 2017 Data Notes

Your order 2017 data is contained in the attached file, “2017_product_data_students.csv” and you should have imported it as “pd2017.” A sample of this file’s type of data is contained below in Table 1 Sample of order data from 2017. (Note your file may or may not have the same data in it.)

Your field definitions follow:

· Month: integer, corresponds to the month of the sale. For example, 5 = May.

· Country: text, should all be USA. (All data in this exercise should be USA.)

· Region: text, represents the regions within the country.

· State: text, USPS state abbreviations. Each state is within one region.

· Product: text. This is the name of a packaged food product.

· Per-unit price: integer. This represents the per-unit price in cents; for example, 300 indicates that Orange Creepies sell for $3.00 per package. (For the purposes of this exercise, disregard all currency formatting and just use 300 to represent $3.00.)

· Quantity: integer. This represents how many items were in that particular order. The first order here was for 49 packages of Orange Creepies.

· Order Total: integer. This is the per-unit price x the quantity. The first line here indicates that 300 x 49 = 14700 (or $147.00) was the price of the first order.

Table 1 Sample of order data from 2017 – SEE ATTACHMENT

2018 Data Notes:

Your order 2018 data is contained in the attached file, “2018_product_data_students.csv”

A sample of this file’s data is contained below as Table 2 Sample of order data from 2018. (Note your file may or may not have the same data in it.)

Your field definitions follow:
· Month: integer, corresponds to the month of the sale. For example, 5 = May.
· Region: text, represents the regions within the country.

· Customer_ID: integer, represents the customer’s unique Customer ID number.

· Product: text. This is the name of a packaged food product.

· Per-unit price: integer. This represents the per-unit price in cents; for example, 363 indicates that PearApple sells for $3.63 per package. (For the purposes of this exercise, you should disregard all currency formatting and just use 363 to represent $3.63.)

· Quantity_1: integer. This represents how many items were in the first shipment of that particular order. This year we had shipping problems, and could often not ship the entire order all at once. Orders were split into two shipments where necessary, and Quantity_1 reflects how many units were shipped first. (Assume all shipments were completed in the month listed, and that no shipments had the first shipment in one month and the second shipment in the subsequent month.)

· Quantity_2: integer. This represents how many items were in the second shipment of that particular order. A 0 indicates a second shipment was not necessary. To get the total number of items shipped, you need to add Quantity_1 and Quantity_2.

· The first line here reflects that PearApple has a first shipment of 25 units, and a second shipment of 92 unit, all within the month of January, for a total of 25 + 92 = 117 units.

Table 2 Sample of order data from 2018 – SEE ATTACHED

2019 Data Notes:

Your order 2019 data is contained in the attached file, “2019_product_data_students.csv.”

A sample of this file’s data is contained below as Table 3 Sample of order data from 2019. (Note your file may or may not have the same data in it.)

Your field definitions follow:
· Month: integer, corresponds to the month of the sale. For example, 5 = May.

· Country: text, represents the country of the customer. Should all be USA.

· Region: text, represents the regions within the country.

· State: USPS code for the 50 United States.

· Product: text. Same as previous years.

· Per-unit price: integer. This represents the per-unit price in cents; same as previous years.

· Quantity: This represents how many items were in that particular order. The first order here was for 95 packages of Only Pancakes.

· Order Subtotal: This represents the order subtotal, calculated as per-unit price x quantity. For example, the first order here reflects a per-unit price of 413 cents x 95 units, for a subtotal of 39,235 (or $392.35).

· Quantity Discount: This represents the new policy (effective January 1, 2019) that all orders 90 units and over will automatically earn a 10% discount. An order of 89 units does not earn the discount; an order of 90 units does earn the discount. All order discounts have been rounded to the nearest penny, so you can assume this field has no decimals in it. In the data below,

o Order 0, on the first line, of 95 Only Pancakes to Florida, did qualify for the Quantity Discount, because an order quantity of 95 exceeded the 90 threshold. The Quantity Discount has been computed as 3924, or 10% of 39235. In this case, the final order total would be 39,235 – 3,924 = 35,311 (or $353.11).

o Order 4, on the fifth line, of 31 Future Toasts to North Carolina, did not qualify for the Quantity Discount. Therefore, the Order total would simply be the Order subtotal.

Table 3 Sample of order data from 2019 – SEE ATTACHED

Part D: Check Your Own Work

1. You can run the following SQL code on your staging table. There is nothing to turn in from this bit. It should yield the following first few rows:

Select region, yearint, monthInt, count(*) from stagingTable where monthInt = 5 group by region, yearInt, monthInt;

2. You can also run the following code to debug. You should get the following rows:

Select yearInt, monthInt, state, customer_id, product_name, orderTotal from stagingTable

where product_name = ‘Big Waffle’ and monthint=4

order by product_name, yearInt, monthInt, state, customer_id, orderTotal;

Now that you’ve debugged your code, it’s time to get a checksum! Run the following code to get a checksum. The checksum will be a number. Put this checksum number on the top of your homework. See table below for help with your CHECKSUM result.

select sum(yearInt * monthInt * orderTotal)%2341 as checksum from stagingTable;

3. Once you get the result of your CHECKSUM look at table below for ways to troubleshoot any issues with your ETL statements. SEE ATTACHMENT

TURN IN:

1. Your output file, called “XX_output_final.csv” where XX are your initials.

2. All the SQL code you used to execute this.

3. A document that contains

a. CHECKSUM: XXX where XXX is the checksum number produced. Put this in big font right on the top.

b. A one page outline of your ETL process. Which functions did you use, and what logic did you follow? This should be at the level that your boss, who has an MBA but not an IT/database background, can follow it. Do not use “computer-ese” here; use regular business English.

USA

2 5 USA

3 9 USA

USA South

5 8 USA West

6 8 USA South

7 1 USA South

8 1 USA South FL

9 12 USA South

10 2 USA Northeast

11 1 USA South NC

Guardian

12 4 USA South DE Only Pancakes

13 3 USA South

2 USA Midwest

Red Hot Chili Peppers 233

8 USA Midwest IL

11 USA Midwest NE

17 7 USA West

Grey Gummies

18 11 USA South NC Future Toast 2

19 10 USA Midwest MO Red Hot Chili Peppers 233 77

20 12 USA South KY

4 USA West CA Big Waffle

22 3 USA West

Cheezos

644

23 7 USA West

Power Gels

24 2 USA Northeast

25 1 USA South NC

26 3 USA South FL

27 9 USA South TN Yellow Zonkers 134 43

28 1 USA West CA

29 10 USA South MD

30 6 USA South TN

31 6 USA West WA Panda Gummies 104 19

32 6 USA South MD Meat B Gone 495 91

045

33 12 USA West CA GMO Guardian 176 46

34 4 USA South NC Big Waffle

35 1 USA South TN Mr Greens

36 4 USA South MD Meat B Gone 495 54

37 8 USA South

38 4 USA South FL

39 4 USA West CA Big Waffle 268 2

40 2 USA Northeast VT Big Waffle 268 68

41 9 USA Northeast MA Ultra-Salty Chips

42 10 USA West CA Funky Pops 380 31

43 3 USA South TN Farm Fresh

44 12 USA South MD Orange Creepies

3000

45 6 USA South DE Mr Greens

46 1 USA Northeast MA Power Gels 193 63

47 11 USA South FL Only Pancakes

5 USA West OR Farm Fresh 365 79

49 2 USA West

Farm Fresh 365 36

50 11 USA South FL

51 12 USA Midwest NE

52 4 USA West HI Big Waffle 268 87

53 7 USA South MD Just The Fruit 160 27

54 4 USA South DE Rotpunkt

55 12 USA West AZ Mr Greens 447 42

56 7 USA West NV Meat B Gone 495 38

57 3 USA West OR Red Rock Candy 189 68

3 USA West OR Ultra-Salty Chips 454 89

59 5 USA Midwest NE Grey Gummies 446 12

60 11 USA South FL Panda Gummies 104 96

5 USA Midwest MO Red Rock Candy 189 77

62 3 USA West NV Just The Fruit 160 40

0 0

63 3 USA Northeast MA Purple Pain 105 3

64 2 USA South NC Only Pancakes 413 66

65 11 USA West

GMO Guardian 176 30

66 3 USA Northeast RI Yellow Zonkers 134 16

67 3 USA Northeast MA Giant Gummies

68 12 USA West WA Yellow Zonkers 134 49

69 10 USA West WA Purple Pain 105 9

12 USA Northeast RI Red Hot Chili Peppers 233 17

7 USA West AZ Giant Gummies 347 68

72 9 USA Midwest NE Red Rock Candy 189 8

73 11 USA South SC Future Toast

74 1 USA West AZ Mr Greens 447 74

75 3 USA South MD Mr Greens 447 13

76 10 USA South NC Power Gels 193 96

77 1 USA West OR Rotpunkt 220 58

78 6 USA South TN Grey Gummies 446 72

79 3 USA West OR Mr Greens 447 73

80 2 USA West CA Giant Gummies 347 25

81 4 USA Midwest MO PearApple 363 4

82 12 USA Northeast RI Big Waffle 268 25

83 6 USA Northeast VT Just The Fruit 160 17

84 10 USA Midwest

Grey Gummies 446 2

85 4 USA South MD Future Toast 253 65

86 7 USA South TN Purple Pain 105 41

87 1 USA West WA Mr Greens 447 98

88 5 USA South DE PearApple 363 39

89 11 USA Midwest NE Panda Gummies 104 2

8 USA Northeast MA PearApple 363 65

91 12 USA South MD Ultra-Salty Chips 454 16

92 11 USA West WA Just The Fruit 160 78

93 9 USA South FL Organiks 257 100

2570

94 9 USA South TN Ultra-Salty Chips 454 76

95 5 USA South FL Nap Be Gone 500 74

96 12 USA West CA Organiks 257 73

97 8 USA Midwest IL Cheezos 372 84

98 4 USA Midwest MO PearApple 363 26

99 7 USA South DE Organiks 257 68

100 9 USA South TN

11 USA Northeast VT PearApple 363 69

12 USA South SC Farm Fresh 365 64

103 6 USA South FL PearApple 363 42

104 8 USA Midwest MI Funky Pops 380 79

105 6 USA South NC Yellow Zonkers 134 25

9 USA West WA Mr Greens 447 94

4 USA Midwest MO Green Lightning 161 91

2 USA West CA Future Toast 253 5

1 USA South MD Power Gels 193 86

5 USA West OR Orange Creepies 300 76

11 USA Midwest MO Purple Pain 105 12 1

112 2 USA Midwest NE Red Rock Candy 189 5

1 USA South FL GMO Guardian 176 75

1 USA Northeast MA Red Hot Chili Peppers 233 20

6 USA Northeast MA Orange Creepies 300 58

10 USA South KY Giant Gummies 347 85

117 5 USA South NC GMO Guardian 176 39

7 USA South TN Purple Pain 105 4 420 0

5 USA West OR Only Pancakes 413 89

3 USA South FL Orange Creepies 300 33

121 5 USA West CA Nap Be Gone 500 26

5 USA Northeast RI Mr Greens 447 71

7 USA South FL Future Toast 253 80

124 9 USA South DE PearApple 363 2 726 0

3 USA South DE GMO Guardian 176 7

126 1 USA Midwest MI GMO Guardian 176 27

127 5 USA South NC Giant Gummies 347 76

128 11 USA South FL Organiks 257 39

2 USA Midwest IL Grey Gummies 446 65

130 12 USA South KY Grey Gummies 446 31

131 3 USA West ID Red Rock Candy 189 65

132 3 USA West ID Funky Pops 380 53

4 USA South TN Just The Fruit 160 4 640 0

134 7 USA Midwest IL Future Toast 253 17

5 USA Midwest MI GMO Guardian 176 36

136 10 USA West AZ Green Lightning 161 67

9 USA West HI Purple Pain 105 17

138 7 USA Northeast RI Yellow Zonkers 134 100

1340

12 USA Northeast MA Rotpunkt 220 33

140 12 USA West HI Green Lightning 161 40

141 4 USA Midwest NE Rotpunkt 220 86

6 USA South MD Orange Creepies 300 8

143 1 USA West CA Organiks 257 80

144 6 USA Midwest NE Panda Gummies 104 79

145 9 USA South MD Big Waffle 268 28

146 11 USA Northeast VT Orange Creepies 300 86

12 USA West NV Farm Fresh 365 63

6 USA West ID Mr Greens 447 100

4470

2 USA Midwest NE Rotpunkt 220 23

8 USA West WA Rotpunkt 220 78

151 11 USA South FL Mr Greens 447 45

152 10 USA South TN Orange Creepies 300 55

8 USA Northeast VT Future Toast 253 46

154 9 USA West OR PearApple 363 42

4 USA South FL Red Rock Candy 189 64

6 USA Midwest MI Nap Be Gone 500 88

4 USA South KY Organiks 257 32 8224 0

2 USA South SC Future Toast 253 83

6 USA South DE Big Waffle 268 54

160 9 USA Midwest NE Just The Fruit 160 94

1504

161 11 USA South TN Grey Gummies 446 45

5 USA South NC Nap Be Gone 500 88 44000 0

163 6 USA West HI Green Lightning 161 78

164 10 USA West WA Farm Fresh 365 10

165 12 USA South SC Crocodile Tears 143 68

6 USA Northeast MA Big Waffle 268 17

7 USA South MD Yellow Zonkers 134 29

12 USA South TN Purple Pain 105 38

10 USA Midwest MO Cheezos 372 99

8 USA South KY GMO Guardian 176 12

171 5 USA Midwest MO Green Lightning 161 64

4 USA South MD Orange Creepies 300 28

173 9 USA South FL PearApple 363 57

174 1 USA Northeast VT Cheezos 372 73

7 USA Northeast VT Cheezos 372 46

176 11 USA South FL PearApple 363 94

6 USA Midwest MI Giant Gummies 347 48

178 8 USA South NC Only Pancakes 413 60

1 USA Midwest MI Rotpunkt 220 39

10 USA South TN Funky Pops 380 64

3 USA West WA Rotpunkt 220 58

8 USA Northeast MA Crocodile Tears 143 68 9724 0

11 USA South FL Big Waffle 268 42

2 USA West AZ Cheezos 372 81

185 2 USA West NV Future Toast 253 89

5 USA Northeast MA Meat B Gone 495 94

4653

187 7 USA Midwest MO Yellow Zonkers 134 61

9 USA South TN Green Lightning 161 87

189 7 USA West WA Purple Pain 105 24

9 USA West CA Panda Gummies 104 20

6 USA West HI Giant Gummies 347 74

2 USA South FL Just The Fruit 160 73

193 5 USA Northeast VT Cheezos 372 81 30132 0

3 USA Northeast RI Big Waffle 268 70

6 USA South MD Panda Gummies 104 40

10 USA South TN Funky Pops 380 97

3686

197 5 USA South KY Only Pancakes 413 7

10 USA South FL Red Hot Chili Peppers 233 68

4 USA South SC Farm Fresh 365 42

200 1 USA South FL PearApple 363 7

201 1 USA South NC Crocodile Tears 143 7

202 1 USA West NV Orange Creepies 300 47

12 USA Midwest IL Crocodile Tears 143 57

5 USA West AZ Farm Fresh 365 22

205 6 USA West AZ Rotpunkt 220 23 5060 0
206 7 USA Midwest MI Orange Creepies 300 71

5 USA South KY Crocodile Tears 143 16

208 12 USA South TN Crocodile Tears 143 23

209 9 USA South TN Ultra-Salty Chips 454 96

1 USA West AZ GMO Guardian 176 60

211 6 USA Midwest IL Red Rock Candy 189 66

10 USA Northeast RI Green Lightning 161 9

213 12 USA Midwest MI Green Lightning 161 52

5 USA South FL Just The Fruit 160 2 320 0

5 USA Midwest MO Rotpunkt 220 85

216 9 USA South KY Power Gels 193 68

7 USA South SC Orange Creepies 300 62

6 USA Midwest MO Purple Pain 105 96

1008

8 USA West HI Cheezos 372 89

220 12 USA South TN Giant Gummies 347 11

2 USA West OR Red Rock Candy 189 3 567 0

222 3 USA Midwest MI Big Waffle 268 19

8 USA Midwest MO Grey Gummies 446 81

224 2 USA Midwest MO Farm Fresh 365 74

225 7 USA West NV Nap Be Gone 500 18

4 USA West ID PearApple 363 14

10 USA West AZ Yellow Zonkers 134 37

228 5 USA South TN GMO Guardian 176 93

229 8 USA South MD Funky Pops 380 97

3686

8 USA Northeast MA Organiks 257 68

1 USA Midwest IL Cheezos 372 74

232 7 USA Northeast MA Only Pancakes 413 6 2478 0
233 4 USA South NC Orange Creepies 300 3 900 0
234 6 USA South TN Panda Gummies 104 59

235 12 USA Midwest MO Power Gels 193 42

1 USA Midwest MI Big Waffle 268 20

5 USA South DE Ultra-Salty Chips 454 70

9 USA Northeast MA Organiks 257 17

4 USA South TN Grey Gummies 446 61

240 10 USA West HI Purple Pain 105 6

2 USA Midwest NE Only Pancakes 413 35

242 9 USA West NV Rotpunkt 220 5

243 4 USA South FL Crocodile Tears 143 88

5 USA South FL Future Toast 253 72

10 USA Midwest MO Only Pancakes 413 26

246 3 USA South SC Orange Creepies 300 81

247 6 USA South FL Crocodile Tears 143 76

248 5 USA South FL Just The Fruit 160 24

12 USA Midwest MO Future Toast 253 15

250 4 USA West NV Organiks 257 10 2570 0

11 USA South NC Cheezos 372 87

252 5 USA West ID Rotpunkt 220 84

253 12 USA West NV Panda Gummies 104 96

998

254 4 USA West WA Yellow Zonkers 134 24

255 1 USA South FL Giant Gummies 347 87

256 4 USA West WA GMO Guardian 176 96

257 1 USA South DE Meat B Gone 495 96

4752

258 2 USA Midwest MO Organiks 257 98

4 USA West CA Red Hot Chili Peppers 233 14

260 9 USA West AZ Meat B Gone 495 88

2 USA Northeast VT Mr Greens 447 87

262 8 USA South DE Yellow Zonkers 134 62

263 7 USA West OR Farm Fresh 365 55

10 USA Northeast MA Rotpunkt 220 80

265 6 USA Northeast RI Grey Gummies 446 41

10 USA Midwest MO Farm Fresh 365 71

6 USA Midwest NE Giant Gummies 347 21

268 9 USA South SC PearApple 363 13

4 USA South DE Rotpunkt 220 2 440 0

270 6 USA Midwest NE Mr Greens 447 42

271 3 USA Northeast MA Giant Gummies 347 68 23596 0
272 10 USA Midwest MO Ultra-Salty Chips 454 37

2 USA South MD Crocodile Tears 143 17

9 USA Midwest MI PearApple 363 46

275 4 USA West WA Mr Greens 447 11

276 7 USA South NC Funky Pops 380 15

4 USA West ID Rotpunkt 220 87

2 USA Midwest NE Farm Fresh 365 89

10 USA Midwest MO Orange Creepies 300 65

280 8 USA South FL Only Pancakes 413 59

3 USA Northeast VT Just The Fruit 160 45

6 USA Northeast VT GMO Guardian 176 19

11 USA West AZ Red Rock Candy 189 29

5 USA South TN Farm Fresh 365 85

285 2 USA South SC Red Rock Candy 189 19

286 7 USA South NC Crocodile Tears 143 25

287 3 USA West CA Green Lightning 161 62

288 12 USA Northeast RI GMO Guardian 176 28

289 5 USA Midwest NE Future Toast 253 70

4 USA Midwest NE Orange Creepies 300 13

1 USA South MD Future Toast 253 83 20999 0

11 USA South FL Power Gels 193 17

11 USA West HI PearApple 363 38

294 9 USA South MD Farm Fresh 365 22 8030 0

6 USA West NV Only Pancakes 413 100

4130

2 USA South MD Green Lightning 161 68

11 USA South MD Mr Greens 447 72

3 USA South KY Crocodile Tears 143 34

3 USA West NV Organiks 257 74

300 3 USA Midwest MO Panda Gummies 104 71

301 7 USA West OR Power Gels 193 28

11 USA South KY Grey Gummies 446 13

9 USA Midwest IL Power Gels 193 42 8106 0

304 12 USA West NV Red Hot Chili Peppers 233 66

305 3 USA South TN Only Pancakes 413 43

10 USA Midwest MO Giant Gummies 347 64

307 1 USA South SC Green Lightning 161 29

308 4 USA West OR PearApple 363 2 726 0

8 USA South TN Funky Pops 380 94

3572

310 8 USA South FL GMO Guardian 176 11 1936 0

11 USA West WA Green Lightning 161 97

312 9 USA South FL Red Hot Chili Peppers 233 59

3 USA South MD Ultra-Salty Chips 454 72

3 USA West HI Purple Pain 105 66

315 11 USA South SC Power Gels 193 4

316 11 USA South TN Purple Pain 105 79

317 3 USA Midwest MI Rotpunkt 220 28

12 USA South DE Big Waffle 268 98

2626

7 USA West OR Nap Be Gone 500 97

4850

320 6 USA South MD Orange Creepies 300 26

321 6 USA South FL Farm Fresh 365 66

4 USA Midwest MI Crocodile Tears 143 43

323 4 USA Midwest NE Rotpunkt 220 33 7260 0
324 9 USA Midwest MO Just The Fruit 160 61

5 USA South DE Giant Gummies 347 38

326 7 USA West WA Just The Fruit 160 8

4 USA West HI Farm Fresh 365 11

328 8 USA West ID Big Waffle 268 37

4 USA West NV Future Toast 253 42

330 11 USA South MD Yellow Zonkers 134 37 4958 0

5 USA South MD Green Lightning 161 47

9 USA Midwest MO Big Waffle 268 71

6 USA South MD Ultra-Salty Chips 454 8

334 4 USA Northeast RI Panda Gummies 104 1 104 0
335 9 USA West WA Power Gels 193 85

336 8 USA West OR Big Waffle 268 97

10 USA South NC Grey Gummies 446 75

6 USA West OR Rotpunkt 220 77

4 USA West CA Farm Fresh 365 42

340 6 USA South FL Power Gels 193 21

341 10 USA West AZ Farm Fresh 365 91

9 USA South DE Grey Gummies 446 83

343 12 USA Midwest IL Green Lightning 161 66 10626 0

4 USA Midwest NE Only Pancakes 413 55

345 1 USA West ID Panda Gummies 104 75 7800 0

1 USA Midwest NE Red Hot Chili Peppers 233 49

347 8 USA South SC Panda Gummies 104 18

5 USA South MD GMO Guardian 176 8

5 USA South MD Yellow Zonkers 134 2 268 0

2 USA Northeast RI Green Lightning 161 45

10 USA Northeast RI Red Rock Candy 189 19 3591 0

352 8 USA West ID Future Toast 253 70 17710 0
353 8 USA Midwest NE Organiks 257 33

11 USA West AZ PearApple 363 58

355 5 USA West CA Panda Gummies 104 95

988

356 8 USA Midwest IL Giant Gummies 347 68 23596 0
357 1 USA Midwest IL Orange Creepies 300 10 3000 0
358 4 USA South MD Power Gels 193 27

359 2 USA South FL PearApple 363 84

360 1 USA West OR Orange Creepies 300 59

361 9 USA Midwest IL Mr Greens 447 66

4 USA West NV Grey Gummies 446 72

363 7 USA South MD Mr Greens 447 12

364 12 USA West CA Crocodile Tears 143 7 1001 0
365 3 USA West HI Just The Fruit 160 34

2 USA West NV Nap Be Gone 500 57

367 10 USA South TN Just The Fruit 160 35

368 2 USA West CA Orange Creepies 300 76

369 9 USA Midwest MO Crocodile Tears 143 5 715 0
370 5 USA South FL Giant Gummies 347 93

3227

12 USA South NC PearApple 363 16

372 12 USA West OR Just The Fruit 160 33

5 USA South FL Mr Greens 447 16

374 6 USA South FL Crocodile Tears 143 7 1001 0

12 USA West AZ PearApple 363 44

9 USA Northeast VT Grey Gummies 446 61 27206 0

5 USA West ID Funky Pops 380 76

378 6 USA South MD Ultra-Salty Chips 454 56

379 8 USA South KY Ultra-Salty Chips 454 3

380 3 USA West OR Rotpunkt 220 55

381 6 USA West HI Orange Creepies 300 67

9 USA Midwest NE Future Toast 253 76

12 USA West OR Purple Pain 105 91

384 2 USA West AZ Cheezos 372 75

1 USA West ID Mr Greens 447 82

8 USA West AZ Nap Be Gone 500 33 16500 0

7 USA West AZ Mr Greens 447 72 32184 0

388 7 USA South TN Purple Pain 105 37

12 USA Midwest MO Purple Pain 105 65

390 8 USA South DE Crocodile Tears 143 48 6864 0

12 USA West AZ Red Hot Chili Peppers 233 38

10 USA South FL Red Rock Candy 189 44

12 USA West OR Green Lightning 161 56

8 USA West AZ Green Lightning 161 67 10787 0

1 USA West HI Cheezos 372 24

396 11 USA Midwest MI Yellow Zonkers 134 33

3 USA West NV Farm Fresh 365 40

7 USA South KY Rotpunkt 220 97

2134

399 9 USA West ID Yellow Zonkers 134 18

400 1 USA Midwest NE Organiks 257 68 17476 0
401 7 USA Northeast MA Big Waffle 268 36

12 USA Northeast MA Panda Gummies 104 38

9 USA Midwest NE Purple Pain 105 68

404 10 USA Midwest NE Nap Be Gone 500 99

4950

405 12 USA West HI Rotpunkt 220 68

1 USA Midwest MO Just The Fruit 160 76

1 USA Midwest MI Red Hot Chili Peppers 233 12

408 11 USA South FL Organiks 257 33 8481 0
409 7 USA West CA Green Lightning 161 81

410 1 USA West ID Meat B Gone 495 13

5 USA West OR Yellow Zonkers 134 20

412 5 USA South TN Crocodile Tears 143 93

413 1 USA West CA Only Pancakes 413 67

4 USA Midwest NE PearApple 363 69

5 USA Northeast VT Ultra-Salty Chips 454 84

416 7 USA West AZ Giant Gummies 347 13

417 3 USA South TN Meat B Gone 495 9 4455 0

6 USA West HI Big Waffle 268 39

7 USA Northeast VT Big Waffle 268 3

420 5 USA West CA Only Pancakes 413 29

9 USA West CA Rotpunkt 220 5 1100 0

422 8 USA South FL Future Toast 253 26

9 USA South DE Mr Greens 447 9

424 10 USA West WA Just The Fruit 160 72

6 USA South TN Just The Fruit 160 39

9 USA South SC Just The Fruit 160 64

7 USA South TN Purple Pain 105 77

7 USA Northeast MA Organiks 257 45

9 USA West NV Cheezos 372 47

430 3 USA South KY Just The Fruit 160 57

431 12 USA West OR Red Hot Chili Peppers 233 54

432 2 USA South TN Cheezos 372 39

9 USA South KY Just The Fruit 160 30

9 USA Midwest MI GMO Guardian 176 76

435 5 USA Northeast VT Yellow Zonkers 134 57

436 8 USA Midwest MO Nap Be Gone 500 35

10 USA South FL Cheezos 372 25

438 7 USA South TN Meat B Gone 495 87

11 USA Northeast MA Future Toast 253 47

440 8 USA South DE Funky Pops 380 70

7 USA Northeast MA Crocodile Tears 143 55

7 USA Midwest NE GMO Guardian 176 85 14960 0

9 USA West WA Nap Be Gone 500 63

4 USA South DE Organiks 257 40

445 9 USA South TN Farm Fresh 365 14

446 12 USA Midwest IL Meat B Gone 495 27

447 3 USA West HI Power Gels 193 51

2 USA South NC Future Toast 253 59

449 1 USA South SC Funky Pops 380 71

450 4 USA Midwest MI Grey Gummies 446 68

451 10 USA Northeast MA Future Toast 253 95

452 5 USA West NV Funky Pops 380 78

6 USA West NV GMO Guardian 176 10 1760 0

454 4 USA Midwest IL Red Hot Chili Peppers 233 44

455 10 USA South SC Funky Pops 380 63

11 USA South SC Purple Pain 105 86

7 USA West HI Crocodile Tears 143 12

11 USA Northeast RI Red Hot Chili Peppers 233 67

1 USA South SC Only Pancakes 413 24

460 7 USA West WA Funky Pops 380 78 29640 0

8 USA Midwest NE Future Toast 253 100

2530

2 USA Northeast RI Meat B Gone 495 87 43065 0

8 USA Midwest IL Farm Fresh 365 89

4 USA South SC Purple Pain 105 44

465 5 USA South FL Red Hot Chili Peppers 233 57

466 10 USA Midwest MO Just The Fruit 160 89

12 USA Northeast RI Ultra-Salty Chips 454 3 1362 0

1 USA Midwest NE Grey Gummies 446 50

4 USA West HI Meat B Gone 495 29

470 7 USA South SC Purple Pain 105 23

471 3 USA West AZ Purple Pain 105 60

472 8 USA Midwest MO Giant Gummies 347 85

1 USA West HI Orange Creepies 300 4

474 6 USA South TN Rotpunkt 220 52

475 3 USA South NC Funky Pops 380 29

476 2 USA Midwest MO Mr Greens 447 95

9 USA West AZ Rotpunkt 220 21 4620 0

478 5 USA Midwest MO Rotpunkt 220 65

479 11 USA South TN Giant Gummies 347 65

480 10 USA West HI Yellow Zonkers 134 35

481 4 USA Northeast MA Organiks 257 83

3 USA West CA Just The Fruit 160 76 12160 0

1 USA Midwest MO Nap Be Gone 500 98

4900

484 3 USA South MD Crocodile Tears 143 71

485 3 USA South NC Rotpunkt 220 3 660 0
486 3 USA Northeast MA PearApple 363 57

5 USA South SC Panda Gummies 104 37

5 USA Northeast MA Yellow Zonkers 134 24

5 USA West AZ Big Waffle 268 35

490 8 USA South SC Just The Fruit 160 22

491 10 USA South MD Organiks 257 45 11565 0
492 4 USA West ID Red Hot Chili Peppers 233 79

5 USA Midwest IL Only Pancakes 413 52

12 USA South TN GMO Guardian 176 78

495 3 USA South MD Future Toast 253 9

496 4 USA West ID Ultra-Salty Chips 454 59

4 USA Midwest NE PearApple 363 84 30492 0

2 USA West OR Rotpunkt 220 49

9 USA West HI Just The Fruit 160 19

	Month	Country	Region	State	Product	Per-Unit Price	Quantity	Order Subtotal	Quantity Discount
																																																																																															0	9	USA	South	FL	Only Pancakes	4	1	3	9	5	39	2	35	39	24
																																												6																																								West			HI	Big Waffle	26	8	93	2	49	92
																																																					Northeast											RI					Grey Gummies	4		46	95	4		23					7	42	37
																																																																							Midwest							NE	Funky Pops	38				10	3	80	380
																																								11																		NC										Future Toast		25	31			78	43
																			WA																							Mr Greens				44											76							33			97
																																	MD																Giant Gummies					34		3				22	32		27
																																						TN												Power Gels							19	2																			12
																Red Hot Chili Peppers																		233								55
																			DE													Nap Be Gone			50							67	33									500
																									MA																Cheezos		3					72					63		234					36
G																																	MO											17				77							13				52
			41						86		355				18
														KY																												Rotpunkt	2			20	4						40
				14																	IL			82	1					91
			15																Panda Gummies																		104		343
					16																			Yellow Zonkers																				134						29	3						88
																						CA																	446		4	326	432
	53		235	2	353
1						79
																					Orange Creepies				30	2								64
			21				2																	68	1									87
																			NV															372							28
																							OR															193	2						89
															VT																								PearApple																					363				57	20	69
																			GMO Guardian																					176	6		1		60
												Meat B Gone														495	16						83
57				62
														Ultra-Salty Chips	4				54		154
																					Farm Fresh		3										65			51
												Red Rock Candy														189		103
	197
							45		450
80								96
																		268		222
4					47	7							59
26					73
																					SC																						Purple Pain																		105	126
																				Crocodile Tears																						143	5720
536
1	8	224
														454		131								66
	117
																				365	1			56
																					300							100			3000
																				447	14					75
	121
																413	26						84
		48		288
																							AZ	13	140
																									Just The Fruit																						160							85		136
																		Organiks																				257	5911
23	316
4		320
																											220		1936
	187							74
18						81
	128
				58		404
5	352
	9						98			99
				61		145
640
	315
27	258
															ID	5	280
2	144
																		347		242
6566
						94
					70		396
							71			2	359
	151
																					253			247	479
3	307
5811
	185	1853
	127
32	112
3	263
8675
1	452
6700
	272
																	MI	892
	164
4	305
	438	4	381
	141
	208
90	23595
		726
	124
		2570
	345
	370
18761
31	248
9438
	174
																		Green Lightning																				161	5796
101		250
102	2	336
	152
30020
	335
106			420	4	202
107			146	465
108	1	265
109		165
110		228
111	260
945
113		132
114		466
115	17	400
116		294
	6864
118
119		367
120	9	900
	130
122	3	173
123	20	240
125	1	232
		475
26372
10023
129		289
	138
12	285
	201
133
4	301
135	6336
	10787
137		178
	1	340
139		7260
6		440
18920
142	2400
	205
8	216
7504
25800
147		229
148		4	470
149		5060
150		171
20115
	16500
153	1	163
15	246
155	1	209
156		44000
157
158		20999
159	14	472
	1504
	200
162
1	255
3650
	9724
166		455
167		388
168		399
169		368	3683
170		211
10	304
172	8400
	206
	271
175	17112
		341	412
177	16656
	2	478
179	8580
180		243
181	1	276
182
183	11	256
184		30132
	225
186		4653
8174
188	14007
	252
190	2080
191	2	567
192	11680
194	18760
195		416
196			3686
2891
198	15844
199	15	330
	254
		1001
1	410
203	8151
204		8030
	213
207	2288
	328
	4	358	435
210	10560
12	474
212	1	449
8372
214
215	18700
1	312
217	18600
218		1008
219	3	310
3817
221
5092
223		361
	270
9000
226	5082
227		4958
16368	1637
36860
230		17	476
231		275
6136
	8106
236	5	360
237		317
238	4	369
239		27206
630
241	1		445
	1100
12584
244	18216
245	10738
2	430
10868
	384
249		379
251		323
18	480
	998
	321
30189
16896	1690
47520
25186	2519
259	3	262
4	356
261	38889
8	308
20075
264		1760
18	286
266	25915
267	7	287
	471
269
18774
16798
273	2	431
274	16698
	491
5700
277	19140
278		324
279	19500
2	436
281	7200
282		334
283	5	481
284	31025
	3591
	357
9982
	492
	17710
290		390
291
292	3281
293	13794
295		4130
296	10948
297		32184
298		486
299	19018
7384
5404
302	5798
303
15	378
17759
306	22208
4669
309		3572
311	15617	1562
1	374
313	32688
314	6930
772
8295
6160
318		2626
319			485
	7800
2	409
322	6149
9760
325	13186
1280
327		401
9916
329		10626
331	7567
332	19028
333	3632
16	405
25996	2600
337	33450
338	16940
339	15330
4053
33215	3322
342	37018
344	22	715
346	11	417
1872
348	1	408
349
350	7245
351
	8481
354	21054
	988
5211
	30492
17700
29502
362	32112
5	364
5440
366	28500
5600
22800
	3227
371	5808
5280
373	7152
375	15972
376
377	28880
25	424
	1362
12100
20100
382	19228
383	9555	956
27900
385	36654
386
387
3885
389	6825
391	8854
392	8316
393	9016
394
395	8928
4	422
397	1	460
398		2134
2412
9648
402	3952
403	7140
	4950
	1	496
406		12160
407	2796
13041
6435
411	2680
13299	1330
27671
414	25047
415	38136
	451
418	10452
419	804
11977
421
6578
423	4023
11520
425	6240
426	10240
427	8085
428		11565
429	17	484
9120
12582
14508
433	4800
434	13376
7638
17500
437	9300
	43065
439	11891
2	660
441	7865
442
443	31500
444	10280
5110
13365
9843
448	14927
26980
30328
24035	2404
	29640
453
10252
23940
456	9030
457	1716
458	15611
459	9912
461		2530
462
463	32485
464		4620
13281
14240
467
468	22300
469	14355
2415
6300
29495
473	1200
11440
11020
42465	4247
477
14300
22555
4690
21331
482
483			490
10153
20691
487	3848
488	3216
489	9380
3520
18407
493	21476
494	13728
2277
26786
497
498	10780
499	3040

1 9 USA

USA

Farm Fresh

USA South

4 12 USA South

5 8 USA South

6 4 USA South

Farm Fresh 3

7 3 USA West

8 4 USA Northeast

9 10 USA South

10 9 USA Northeast MA

10 USA South FL

12 1 USA West

Orange Creepies

13 8 USA South

7 USA West AZ Farm Fresh

15 2 USA West

16 6 USA South MD Farm Fresh 365 16

17 8 USA Northeast RI

100

8 USA South MD

7 USA West

20 5 USA South SC Power Gels 193 29

12 USA South NC Future Toast

3 USA South MD Ultra-Salty Chips

23 3 USA West NV Giant Gummies 347

12 USA South FL Future Toast 253 17

25 1 USA

26 6 USA West NV

27 4 USA South DE

28 12 USA West AZ Only Pancakes

29 2 USA Northeast

Future Toast 253 59

30 10 USA Midwest

Future Toast 253 42

2 USA South TN Just The Fruit

32 4 USA West

9 USA South DE Just The Fruit

34 7 USA Midwest

35 1 USA West CA Orange Creepies 300 8

36 7 USA South DE

37 7 USA Midwest

Orange Creepies 300

38 6 USA South FL Yellow Zonkers

39 6 USA South MD Giant Gummies 347 71

40 10 USA South FL Grey Gummies

41 11 USA South FL Just The Fruit 160 10

42 4 USA South NC

43 4 USA Northeast VT Power Gels 193 78

44 6 USA South TN Nap Be Gone 500

45 4 USA South NC

46 6 USA Midwest MO GMO Guardian

47 5 USA Northeast MA Only Pancakes 413 2

48 3 USA Midwest IL Grey Gummies 446 1 446
49 8 USA South MD Rotpunkt 220 51

50 6 USA Midwest MI Meat B Gone 495 38

51 12 USA Midwest MI Only Pancakes 413

52 3 USA South DE Power Gels 193 38

53 10 USA West WA Panda Gummies 104 30

54 2 USA West CA Nap Be Gone 500 96

6 USA Midwest MI GMO Guardian 176

56 8 USA South FL

57 8 USA South MD GMO Guardian 176 60

58 5 USA South TN Rotpunkt 220

59 9 USA South FL Meat B Gone 495 23

60 2 USA West

Purple Pain 105 93

61 9 USA West HI Meat B Gone 495

62 11 USA South KY Rotpunkt 220 61

63 9 USA West WA Panda Gummies 104 19

64 9 USA West OR

65 12 USA South FL Purple Pain 105 14

66 2 USA Northeast VT Giant Gummies 347 30

67 8 USA Northeast MA Nap Be Gone 500 5

68 8 USA South NC Future Toast 253

69 11 USA Midwest MO Meat B Gone 495 22

70 3 USA Northeast RI Just The Fruit 160 29

71 8 USA South MD Red Rock Candy 189 71

72 9 USA South TN Funky Pops

73 12 USA South DE Nap Be Gone 500 64

4 USA South DE Ultra-Salty Chips 454 67

4 USA West OR Orange Creepies 300 15

76 6 USA South DE Red Rock Candy 189 9

77 11 USA West NV Red Hot Chili Peppers 233

78 6 USA South FL

7 USA West CA Cheezos

80 9 USA South MD PearApple

81 10 USA South MD Cheezos 372 26

82 9 USA South MD Power Gels 193 30

83 12 USA South KY Power Gels 193 73

84 12 USA Midwest NE Ultra-Salty Chips 454 32

85 8 USA Midwest MO GMO Guardian 176 63

4 USA West WA Giant Gummies 347 27

87 10 USA South FL Ultra-Salty Chips 454 86

88 6 USA South NC Purple Pain 105 89

89 9 USA Midwest IL Green Lightning 161 54

90 1 USA South FL Panda Gummies 104 14

91 7 USA South DE Power Gels 193 60

92 6 USA South MD Rotpunkt 220 36

93 7 USA West HI Yellow Zonkers 134 59

94 11 USA South SC Nap Be Gone 500 39

95 11 USA South FL Yellow Zonkers 134 67

96 11 USA South FL GMO Guardian 176 13

97 8 USA Midwest NE Ultra-Salty Chips 454 62

6 USA Northeast MA Green Lightning 161 92

7 USA South SC Cheezos 372 54

100 3 USA West HI PearApple 363 64

4 USA South FL Mr Greens 447 31

10 USA Northeast RI Funky Pops 380 33

10 USA West CA Farm Fresh 365 8 2920

104 3 USA South TN Ultra-Salty Chips 454 24

105 1 USA South SC Only Pancakes 413 31

106 2 USA West CA Red Hot Chili Peppers 233 36

3 USA South FL Panda Gummies 104 13

108 2 USA West NV Funky Pops 380 46

2 USA Northeast RI Yellow Zonkers 134 7

110 9 USA Northeast MA Red Hot Chili Peppers 233 12

111 7 USA Midwest NE Future Toast 253 90 2

112 11 USA West CA Power Gels 193 87

113 6 USA South MD Mr Greens 447 75

7 USA West OR Red Rock Candy 189 83

115 12 USA South MD Panda Gummies 104 26

116 4 USA Midwest IL GMO Guardian 176 89

1 USA Midwest MI Just The Fruit 160 7 1120

3 USA West ID Giant Gummies 347 80

9 USA West AZ Nap Be Gone 500 35

120 3 USA South KY Green Lightning 161 36

2 USA Midwest IL Farm Fresh 365 85

122 5 USA South TN Power Gels 193 90

5 USA West HI Green Lightning 161 48

3 USA South KY GMO Guardian 176 73

125 11 USA South FL Only Pancakes 413 5

3 USA South KY Future Toast 253 77

9 USA West OR Funky Pops 380 20

128 11 USA Midwest MO Farm Fresh 365 89

7 USA West CA Power Gels 193 55

130 9 USA South SC Green Lightning 161 37

6 USA South SC PearApple 363 94

8 USA South KY Giant Gummies 347 78

12 USA South DE Panda Gummies 104 16

134 4 USA South MD Grey Gummies 446 61

135 3 USA Northeast VT Purple Pain 105 7

4 USA West HI Organiks

1 USA West CA Funky Pops 380 12

138 7 USA West HI Panda Gummies 104 5 520

1 USA Northeast MA PearApple 363 1 363

140 4 USA Midwest MI Panda Gummies 104 11

141 5 USA Midwest NE GMO Guardian 176 38

3 USA Midwest IL Giant Gummies 347 100

143 2 USA Northeast VT Yellow Zonkers 134 77

144 8 USA West AZ Orange Creepies 300 44

145 3 USA Midwest NE PearApple 363 36

3 USA South DE Cheezos 372 91

147 3 USA South FL Future Toast 253 93

148 3 USA West HI GMO Guardian 176 98

149 6 USA Northeast VT Nap Be Gone 500 77

150 9 USA South TN Only Pancakes 413 39

10 USA Northeast RI Orange Creepies 300 41

3 USA South FL Panda Gummies 104 2

11 USA West ID Orange Creepies 300 18

154 8 USA West AZ Big Waffle

2 USA West AZ Meat B Gone 495 1 495

156 1 USA South FL Orange Creepies 300 94

5 USA South MD Funky Pops 380 69

158 5 USA South TN Ultra-Salty Chips 454 54

4 USA West CA Mr Greens 447 37

160 4 USA Midwest MO Red Hot Chili Peppers 233 93

161 10 USA South DE PearApple 363 63

8 USA Midwest NE Purple Pain 105 34

1 USA South TN Future Toast 253 42

12 USA West HI PearApple 363 80

165 6 USA South TN Grey Gummies 446 95

166 10 USA West WA Organiks 257 1 257
167 7 USA South TN Power Gels 193 96

8 USA West OR Purple Pain 105 7 735

12 USA West WA Crocodile Tears 143 64

170 7 USA Midwest IL Organiks 257 77

10 USA South DE Yellow Zonkers 134 61

172 11 USA Northeast MA Ultra-Salty Chips 454 43

173 3 USA West WA Organiks 257 40

174 11 USA South FL Purple Pain 105 14

175 7 USA Northeast MA Red Rock Candy 189 25

176 9 USA South NC Farm Fresh 365 62

10 USA South TN Farm Fresh 365 22

1 USA South DE Grey Gummies 446 29

7 USA Midwest MO Meat B Gone 495 6

3 USA Midwest NE Panda Gummies 104 3 312

9 USA West HI Farm Fresh 365 76

4 USA South KY Mr Greens 447 53

1 USA West NV Giant Gummies 347 5

8 USA Midwest MO Rotpunkt 220 25

185 2 USA South FL Organiks 257 66

1 USA South KY Power Gels 193 75

8 USA South MD Mr Greens 447 79

188 7 USA South KY Nap Be Gone 500 57

189 7 USA Midwest NE Mr Greens 447 36

11 USA South TN Farm Fresh 365 5

11 USA Midwest IL Meat B Gone 495 42

12 USA Midwest NE Rotpunkt 220 30

193 1 USA South NC Red Rock Candy 189 88

194 7 USA West WA Yellow Zonkers 134 53

195 10 USA South SC Organiks 257 10

12 USA South SC Ultra-Salty Chips 454 7

197 5 USA Midwest MO Crocodile Tears 143 94

2 USA South MD Grey Gummies 446 33

9 USA South FL Funky Pops 380 9 3

200 12 USA Northeast MA Cheezos 372 70

8 USA South DE Cheezos 372 65

202 11 USA Northeast VT Organiks 257 32

11 USA West WA Purple Pain 105 65

12 USA West WA Only Pancakes 413 38

9 USA Midwest IL Green Lightning 161 51

206 5 USA South TN Crocodile Tears 143 48

207 10 USA South FL Orange Creepies 300 81

208 3 USA Midwest NE Purple Pain 105 27

4 USA South KY Yellow Zonkers 134 97

1 USA Midwest NE Only Pancakes 413 26

211 7 USA South FL Orange Creepies 300 71

11 USA South DE Orange Creepies 300 66

213 6 USA Midwest NE Giant Gummies 347 45

11 USA South TN Crocodile Tears 143 67

6 USA South KY PearApple 363 80 29040

216 7 USA Midwest MO Nap Be Gone 500 10

6 USA West HI Giant Gummies 347 92

5 USA South TN Red Rock Candy 189 86

3 USA Midwest NE Cheezos 372 12

220 5 USA South TN Ultra-Salty Chips 454 63

2 USA West AZ Nap Be Gone 500 89

10 USA Northeast RI Crocodile Tears 143 18

12 USA Midwest MO Purple Pain 105 66

224 11 USA Northeast RI Ultra-Salty Chips 454 83

9 USA South FL Future Toast 253 94

226 2 USA West HI Funky Pops 380 58

227 11 USA Midwest IL Big Waffle 268 41

228 11 USA Midwest NE Only Pancakes 413 92

3 USA South SC Power Gels 193 29

230 7 USA Midwest IL Farm Fresh 365 51

6 USA Midwest MO Nap Be Gone 500 43

232 11 USA South MD Purple Pain 105 23

233 5 USA South TN GMO Guardian 176 73 12848
234 5 USA South TN PearApple 363 91

235 5 USA South TN Red Rock Candy 189 38

236 4 USA South NC Meat B Gone 495 45

237 2 USA West OR Organiks 257 57

2 USA Midwest IL Grey Gummies 446 15

12 USA West WA Mr Greens 447 4

240 5 USA Midwest NE Meat B Gone 495 78

241 7 USA Midwest NE Grey Gummies 446 81

12 USA South KY Crocodile Tears 143 73

243 2 USA West WA Orange Creepies 300 44 13200

4 USA South SC Purple Pain 105 1 105

245 9 USA Northeast RI Panda Gummies 104 15

4 USA South FL PearApple 363 1 363

8 USA Northeast MA Funky Pops 380 25

248 11 USA Midwest IL Red Rock Candy 189 57

7 USA West AZ Green Lightning 161 6

250 11 USA West HI Panda Gummies 104 84

3 USA Midwest MI Future Toast 253 92

2 USA South DE Future Toast 253 11

253 4 USA South TN Purple Pain 105 54

254 4 USA Northeast MA PearApple 363 64

5 USA South MD Farm Fresh 365 56

6 USA South FL Orange Creepies 300 31

257 11 USA South FL Green Lightning 161 46

2 USA Midwest MI Organiks 257 49

259 10 USA Northeast RI Green Lightning 161 38

260 11 USA West AZ Farm Fresh 365 60

9 USA South MD Future Toast 253 95

262 7 USA West WA Only Pancakes 413 64

1 USA West ID Mr Greens 447 23

264 3 USA South FL Red Rock Candy 189 52

11 USA West CA Green Lightning 161 21

10 USA West CA Ultra-Salty Chips 454 52

12 USA West HI Organiks 257 16

268 11 USA Midwest MO Yellow Zonkers 134 64

12 USA Midwest MI Yellow Zonkers 134 91

270 2 USA South KY Farm Fresh 365 14

5 USA South TN Red Rock Candy 189 88 16632

272 10 USA Midwest MI Only Pancakes 413 65

3 USA Northeast VT Red Hot Chili Peppers 233 87

2 USA South FL Funky Pops 380 19

275 3 USA West AZ Red Rock Candy 189 15

276 9 USA Northeast MA Mr Greens 447 41

277 1 USA West AZ Grey Gummies 446 56

278 4 USA West NV Green Lightning 161 88

279 3 USA South TN Funky Pops 380 80

280 8 USA West NV Orange Creepies 300 5 1500
281 6 USA South TN Green Lightning 161 54 8694
282 12 USA West OR Rotpunkt 220 70

283 7 USA West AZ Grey Gummies 446 35

284 3 USA South FL Grey Gummies 446 52

285 3 USA West HI Giant Gummies 347 44

286 4 USA Midwest MI Organiks 257 96

6 USA South FL Green Lightning 161 79

12 USA South FL Future Toast 253 22

6 USA Midwest MO Funky Pops 380 61

290 3 USA West OR Rotpunkt 220 90 19800

2 USA South FL GMO Guardian 176 9

292 5 USA South NC Green Lightning 161 82

293 7 USA West AZ Power Gels 193 29 5597

9 USA West OR Farm Fresh 365 34

8 USA South NC Yellow Zonkers 134 51

1 USA West NV Only Pancakes 413 43

297 9 USA Northeast RI PearApple 363 82

3 USA West WA Just The Fruit 160 70

299 12 USA West AZ Ultra-Salty Chips 454 39

300 10 USA Midwest NE Rotpunkt 220 17

301 12 USA Midwest IL PearApple 363 65

3 USA West NV Farm Fresh 365 43

303 12 USA West NV Giant Gummies 347 31

304 2 USA Midwest MO Big Waffle 268 1 268

4 USA Northeast MA Big Waffle 268 62

306 8 USA Northeast MA Green Lightning 161 12

3 USA South KY Farm Fresh 365 4

6 USA West OR Nap Be Gone 500 87

4 USA Northeast MA PearApple 363 37

310 5 USA Midwest MO Red Hot Chili Peppers 233 32

3 USA South MD Big Waffle 268 38

312 11 USA West AZ Farm Fresh 365 67

313 6 USA Midwest IL PearApple 363 37 13

5 USA South FL Farm Fresh 365 56 20440

3 USA South MD Power Gels 193 34

8 USA South KY Green Lightning 161 10 1610

317 11 USA West NV Future Toast 253 67

318 5 USA West HI Organiks 257 99

319 11 USA South DE Mr Greens 447 85

320 9 USA Northeast RI Rotpunkt 220 37

1 USA West OR Purple Pain 105 86

3 USA South TN Rotpunkt 220 72

323 3 USA Northeast VT Grey Gummies 446 92

4 USA West WA Rotpunkt 220 97

7 USA West AZ Farm Fresh 365 38

11 USA Midwest IL Cheezos 372 90

327 12 USA West NV Red Hot Chili Peppers 233 39

5 USA West OR Just The Fruit 160 90

7 USA West ID Nap Be Gone 500 25

7 USA South FL Funky Pops 380 65

9 USA South TN Funky Pops 380 88

2 USA South FL Funky Pops 380 57

8 USA West WA Farm Fresh 365 81

334 6 USA West CA Giant Gummies 347 68

335 4 USA Northeast VT Organiks 257 26

336 6 USA South TN Orange Creepies 300 58

2 USA South NC Farm Fresh 365 33

338 8 USA Northeast MA Purple Pain 105 44

8 USA South TN Power Gels 193 50

340 11 USA West HI Power Gels 193 21

341 7 USA South TN Red Rock Candy 189 15 2835
342 6 USA South NC Yellow Zonkers 134 20

343 1 USA Midwest MO Giant Gummies 347 46

344 3 USA Northeast MA Panda Gummies 104 11

345 1 USA South SC Grey Gummies 446 29

4 USA South KY Cheezos 372 28

347 2 USA South DE Big Waffle 268 93

348 5 USA Midwest MO Red Hot Chili Peppers 233 94

11 USA West HI Green Lightning 161 53

350 11 USA South SC Green Lightning 161 33

12 USA West NV Giant Gummies 347 17

4 USA Northeast RI Meat B Gone 495 55

12 USA West AZ Yellow Zonkers 134 29

9 USA West WA Organiks 257 80

11 USA West OR Purple Pain 105 5

5 USA Midwest IL Rotpunkt 220 33

357 6 USA Northeast VT Red Rock Candy 189 8

2 USA South NC Green Lightning 161 16

359 1 USA West WA Rotpunkt 220 96

360 4 USA South TN Green Lightning 161 4

361 7 USA South MD Red Hot Chili Peppers 233 11

6 USA West HI Cheezos 372 18

363 1 USA Northeast VT Red Rock Candy 189 9

7 USA West CA Just The Fruit 160 47

365 9 USA Northeast VT Panda Gummies 104 91

1 USA South TN Future Toast 253 3 759

6 USA Midwest NE Mr Greens 447 21

9 USA South KY Farm Fresh 365 83

369 10 USA West ID Red Hot Chili Peppers 233 9

370 4 USA South NC Rotpunkt 220 22

10 USA Midwest MI Ultra-Salty Chips 454 82

372 8 USA West CA Farm Fresh 365 97

1 USA South MD Mr Greens 447 53 23691

374 12 USA South FL Rotpunkt 220 90 19800

1 USA West HI Red Rock Candy 189 6

376 8 USA West OR Ultra-Salty Chips 454 76

2 USA South KY Big Waffle 268 92

378 2 USA Midwest IL Power Gels 193 85

379 8 USA Midwest IL GMO Guardian 176 53

380 4 USA Midwest NE Just The Fruit 160 3 480
381 5 USA South KY Green Lightning 161 59

11 USA Northeast MA Only Pancakes 413 24

10 USA Midwest IL Cheezos 372 81

1 USA West OR Cheezos 372 58

385 12 USA South FL Ultra-Salty Chips 454 33

386 4 USA South FL Future Toast 253 5

387 10 USA West CA Cheezos 372 22

388 1 USA West CA Red Hot Chili Peppers 233 42

4 USA South FL Just The Fruit 160 49

390 3 USA South SC Red Rock Candy 189 80

4 USA South FL Funky Pops 380 72

5 USA South FL Yellow Zonkers 134 94

6 USA South TN Ultra-Salty Chips 454 99

9 USA West OR Farm Fresh 365 49

4 USA South DE Funky Pops 380 21

10 USA South KY Funky Pops 380 73 27740

5 USA South KY Nap Be Gone 500 13

3 USA South SC Orange Creepies 300 54

12 USA South NC Farm Fresh 365 67 24455

400 9 USA West CA Crocodile Tears 143 69

3 USA Midwest NE Green Lightning 161 60

9 USA South DE Organiks 257 50

403 1 USA West ID Mr Greens 447 10

404 8 USA West ID Giant Gummies 347 92

405 9 USA South NC Future Toast 253 86

406 9 USA Midwest IL Just The Fruit 160 46 7360

7 USA West HI Organiks 257 66 16962

1 USA South TN Red Hot Chili Peppers 233 23

10 USA South FL Meat B Gone 495 83

410 7 USA Northeast VT PearApple 363 88

411 7 USA Midwest IL Meat B Gone 495 43

412 9 USA South FL Red Rock Candy 189 65

413 8 USA South KY Nap Be Gone 500 92

3 USA South FL Only Pancakes 413 41

415 6 USA South FL Power Gels 193 33

416 3 USA West NV Panda Gummies 104 27

5 USA West AZ Future Toast 253 94

8 USA Midwest IL Just The Fruit 160 98

3 USA South KY Funky Pops 380 52

420 4 USA Northeast MA Red Hot Chili Peppers 233 77

1 USA South MD Red Rock Candy 189 74

2 USA West WA Funky Pops 380 89

3 USA Midwest MI Just The Fruit 160 66 10560

2 USA Midwest MI Meat B Gone 495 21

9 USA South KY Meat B Gone 495 30

6 USA South TN Power Gels 193 41

2 USA South DE Panda Gummies 104 98

9 USA Midwest IL Big Waffle 268 1 268

11 USA West ID Grey Gummies 446 3

3 USA Midwest MI Big Waffle 268 82

431 9 USA South TN Rotpunkt 220 7 1540

2 USA Northeast MA Organiks 257 86

8 USA Midwest MO PearApple 363 59

5 USA South FL PearApple 363 28

2 USA West OR Only Pancakes 413 12

5 USA Midwest NE PearApple 363 9

2 USA West AZ Ultra-Salty Chips 454 100

5 USA South MD Orange Creepies 300 38

439 7 USA South FL Funky Pops 380 73 27740
440 5 USA South DE GMO Guardian 176 80

2 USA Midwest MI Cheezos 372 44

6 USA West WA Purple Pain 105 3 315

443 4 USA South NC Big Waffle 268 13

10 USA Midwest MI PearApple 363 7

445 3 USA Midwest NE Crocodile Tears 143 90

446 8 USA West CA Funky Pops 380 3 1140
447 11 USA South KY PearApple 363 27

10 USA South NC Farm Fresh 365 63

449 5 USA Northeast RI Grey Gummies 446 75

450 6 USA West AZ PearApple 363 11

6 USA West WA Only Pancakes 413 88

4 USA West ID Farm Fresh 365 31

2 USA Midwest MO Big Waffle 268 72

454 4 USA South DE Future Toast 253 56

455 12 USA Midwest NE Green Lightning 161 78

456 11 USA South KY PearApple 363 3 1089

11 USA Northeast VT Power Gels 193 75 14475

3 USA South TN Panda Gummies 104 56

9 USA West HI Rotpunkt 220 66

460 9 USA South DE GMO Guardian 176 95

11 USA Midwest NE Big Waffle 268 37

462 10 USA South FL Funky Pops 380 25 9500

12 USA West WA Funky Pops 380 20 7600

464 2 USA Midwest IL Crocodile Tears 143 42

465 12 USA West OR Funky Pops 380 97

10 USA West NV Crocodile Tears 143 92

467 10 USA West NV Mr Greens 447 12

12 USA West WA Organiks 257 13

5 USA Northeast MA Rotpunkt 220 5

470 1 USA South TN Mr Greens 447 63

471 9 USA Midwest IL Meat B Gone 495 63

472 7 USA Northeast VT Funky Pops 380 61 23180

2 USA Midwest MI Red Hot Chili Peppers 233 64

4 USA South TN Crocodile Tears 143 83

475 5 USA South KY Giant Gummies 347 7

8 USA Northeast MA Cheezos 372 72

4 USA West CA Farm Fresh 365 43 15695

11 USA Midwest IL Organiks 257 24

9 USA South NC Cheezos 372 76

480 7 USA Midwest NE Red Rock Candy 189 86 16254
481 5 USA South TN Red Hot Chili Peppers 233 44

12 USA South TN Just The Fruit 160 17

7 USA South KY Future Toast 253 10

484 9 USA South DE Farm Fresh 365 82

485 3 USA West CA Orange Creepies 300 54 16200

6 USA South TN Future Toast 253 25

12 USA Midwest MO Panda Gummies 104 87

8 USA South TN Mr Greens 447 52

1 USA West NV Power Gels 193 83

5 USA Midwest MO Cheezos 372 55

491 12 USA South FL GMO Guardian 176 66

492 4 USA West CA Red Rock Candy 189 92

3 USA West WA PearApple 363 30

6 USA Northeast RI Crocodile Tears 143 23

495 9 USA Midwest MI Cheezos 372 46

3 USA Northeast MA Meat B Gone 495 26 12870

497 6 USA West HI Crocodile Tears 143 66

498 10 USA South TN Orange Creepies 300 57

499 8 USA West NV Power Gels 193 65

	Month	Country	Region	State	Product	Per-Unit Price	Quantity	Order Total
0																																																					7																																														USA	West	CA	Orange Creepies	3	4	9	1	47
																																																						Northeast											RI												Farm Fresh	3										6	5	49	17	8	85
																																											2																												10																							South	TN	36	36	50
																																															12																													FL				Organiks	25		27	69		39
																										MD																									PearApple	3						63					83					30		29
																												KY													Big Waffle								26							13
														SC								65								82	29					93
																				AZ																											Funky Pops										38			62						23			56
																							MA																			Ultra-Salty Chips		4										54			28	12			71
																									DE															Just The Fruit								16				48
														Yellow Zonkers				34													92	12				32
																																																	11														Purple Pain									105	7	45
																		NV																				300									67						20			100
																			NC																Grey Gummies									44							89	3					96						94
				14																												365								43							15				95
																						HI																							Future Toast					2					53		111
				58		40
																	Giant Gummies																			347	34				70
			18																Nap Be Gone																500	1			35
		19										ID																							Power Gels																							193				42					81
5						59
					21																			253	2	150
					22																		454						72								64
						61	2	116
			24	4	301
																																																																											Midwest				MI			Cheezos		37	59		52
																							Green Lightning																									161		154
																Only Pancakes								41	10							73
															413	4	130
															VT		149
																									NE		106
					31			1					60			112
																							WA																	Meat B Gone																		495	292
							33												160	1	120
																										IL																				Rotpunkt																	220	11	8				80
	240
																		Panda Gummies																				104			68	7072
																					MO						78		234
														134		125
2							46
																446	20					51
1600
															Red Hot Chili Peppers																	233	1					91
15054
		84	4	200
															GMO Guardian				1				76							88
												176		404
826
1	122
	188
					97		400
7	334
		312
		480
			55							57	10032
													Crocodile Tears															143	715
	10560
									66	14	520
	113
																		OR	9765
						77	38	115
1		342
	197
																				Red Rock Candy																						189		173
	147
10	410
	250
								90			227
	108
	464
1	341
																										380		254
	320
	74		304
				75		450
	170
					87		202
																Mr Greens																		447	24	138
		79																			372	7	440
																										363		141
9672
5790
	140
	145
	110
						86	9	369
	390
9	345
	8694
1	456
1	158
7920
7906
	195
8978
	228
28	148
			98	1	481
		99	20088
	232
101	1	385
102	12540
103
	1089
	128
8	388
107		135
	174
109	938
	279
277
	167
	335
114		156
	270
15664
117
118	27760
119		175
5796
121		310
17	370
123	7728
124		1	284
	206
126		194
127		7600
3	248
129	10615
5957
131	3	412
132	27066
133		166
	272
	735
136																					257	1	336
137	4560
139
1	144
6688
142	3	470
10	318
	13200
1	306
146		338
	235
	172
38500
	1610
151	1	230
152		208
153	5400
														268	7	236
155
	282
157		262
	245
159		165
	216
2	286
162		357
163	10626
164			290
4	237
	185
168
169	9152
19789
171	8174
19522
10	280
1470
	472
	226
177	8030
178	1	293
179		297
180
181			27740
182		23691
183	1735
184	5500
	16962
186		14	475
187	35	313
	285
16092
190	1825
191		207
192	6600
	16632
7102
2570
196		317
1	344
198	1	471
199	420
	260
201		241
8	224
203	6825
204	15694
205	8	211
6864
	243
		283
209	1	299
210	10738
	213
212			19800
15615
214	9581
215
5000
217		319
218		16254
219	4464
28602
221		445
222	2574
223	6930
	376
225	2	378
22040
10988
	379
229		5597
18615
231	2	1500
2	415
3	303
7182
22	275
14649
238	6690
239	1788
	386
	361
242	10	439
244
1560
246
247		9500
10773
249	966
8736
251	23	276
252		278
5670
2	323
255		20440
256	9300
7	406
258	1	259
6118
21900
261	2	403
	264
263	10	281
9828
265	3	381
266	2	360
267		411
8576
269	12194
5110
271
26845
273	20271
274	7220
2835
18	327
2	497
1	416
30400
	1540
15610
23192
15268
2	467
287	12719
288	5566
289		23180
291	1584
13202
294	12410
295	6834
296	17	759
29766
298	11200
17706
	374
2	359
302		15695
10757
305	16616
1932
307	1	460
308	4	350
309		1	343
7456
311	10184
	24	455
431
314
	315	6562
316
16951
25	443
37995
8140
321	9030
322	15840
41032
324	21	340
325	1	387
326	3	348
9087
328	14400
329	12500
330	24700
331	33440
332	21660
333	29565
23596
6682
17400
337	12045
	462
339	9650
	405
2680
15962
1144
12934
346	10416
2	492
21902
349	8533
5313
351	5899
352	27225
353	3886
354	20560
355	525
356	7260
1512
358	2576
21120
644
2563
362	6696
1701
364	7520
9464
366
367	9387
368	30295
2097
	484
371	37228
35405
373
375	1134
34504
377	2	465
16405
9328
9	499
382	9912
383	30132
384	21576
1	498
1265
8184
9786
389	7840
15120
391	2	7360
392	12596
393		449
394	17885
395	7980
396
397	6500
398		16200
399
9867
401	9660
402	12850
4470
31924
21758
407
408	5359
409	41085
31944
21285
12285
46000
414	16933
6369
2808
417	23782
418	15680
419	19760
17941
421	13986
422	33820
423
424	10395
425	1	485
426	7913
427	10192
428
429	1338
430	21976
432	22102
433	21417
434	10164
435	4956
436	3267
437	45400
438		1140
14080
441	16368
442
3484
444	2541
	12870
9801
448	22995
33450
3993
451	36344
452	11315
453	19296
14168
12558
457
458	5824
459	14520
16720
461	9916
463
6006
36860
466	13156
5364
468	3341
469	1100
28161
31185
473	1	491
474	11869
2429
476	26784
477
478	6168
479	28272
10252
482	2720
483	2530
29930
486	6325
487	9048
488	23244
489	16019
490	20460
11616
17388
493	10890
494	3289
17112
496
9438
17100
12545

1 5

2 8

3 4 Midwest

4 13

5 1 South

6 8 Midwest

7 4 Midwest

8 5 West

9 12 South

11 West

11 12 Midwest

Funky Pops

12 7 South

Mr Greens

13 3 South

14 6 Midwest

Crocodile Tears

15 4 Northeast

Giant Gummies

16 1 West

Just The Fruit

17 5 Midwest

18 11 Midwest

Just The Fruit

17 97

5 South

20 3 Midwest

Funky Pops

32 32

21 6 Midwest

75 68

22 10 South

23 3 Midwest 52

40 89

3 West

Red Rock Candy

90 10

25 1 South 60

26 4 South

6 West 30

28 3 Midwest

Funky Pops 380 21 52

29 11 Northeast

55 79

30 5 West

45 84

2 South

61 30

32 12 West

Just The Fruit 160 34 76

33 3 Northeast

61 6

34 11 South

Power Gels 193 89

35 9 South

Grey Gummies

36 5 Northeast

Rotpunkt

37 11 Midwest

Meat B Gone

5 99

38 11 Northeast

35 6

39 4 South

Rotpunkt 220 92 20

40 11 Northeast

Purple Pain 105 54 1

41 6 West

Just The Fruit 160 40 20

7 Midwest

Just The Fruit 160 13 4

43 4 Midwest

Ultra-Salty Chips 454 62 22

44 12 Northeast

Mr Greens

93 91

45 2 West 19 Funky Pops 380 12 29
46 4 West

Only Pancakes 413 75 88

47 4 South

Mr Greens 447 35 64

48 12 Northeast

Only Pancakes 413 92 30

49 4 Midwest

Mr Greens 447 67 77

50 6 Midwest

Red Rock Candy 189 9 40

51 8 West

Farm Fresh

73 98

52 7 West

Mr Greens 447 30 12

7 South

Funky Pops 380 37 45

54 5 South

Red Rock Candy 189 71 80

55 8 Midwest

Crocodile Tears

76 87

56 5 West

Panda Gummies 104 56

57 12 Midwest

Panda Gummies 104 20 87

58 12 Midwest

Purple Pain 105

59 3 Northeast

Meat B Gone 495 63 9

60 4 South

Yellow Zonkers 134 87 54

61 5 Midwest

Mr Greens 447

62 12 Northeast

GMO Guardian

10 66

63 6 South

Mr Greens 447 49 4

64 10 Midwest 33 GMO Guardian 176 25 61
65 4 West

Just The Fruit 160 9 48

66 6 South

Grey Gummies 446 37 66

67 6 South

Mr Greens 447 56 37

68 4 Northeast

Purple Pain 105 85 16

69 11 West

Nap Be Gone 500 15 42

70 4 South

Power Gels 193 80 80

71 12 Midwest

76 52

72 12 West

Ultra-Salty Chips 454 88 34

73 9 South

Ultra-Salty Chips 454 96 67

74 4 Northeast

Just The Fruit 160 33 98

75 8 Midwest

Orange Creepies

84 41

76 12 South

Ultra-Salty Chips 454 44 44

77 9 Northeast

Crocodile Tears 143 50 76

2 Midwest

Red Rock Candy 189 71 89

79 8 Northeast

Green Lightning

99 32

80 12 Midwest

Meat B Gone 495 92 20

81 2 Northeast

Organiks 257 78 45

82 4 West

Giant Gummies 347 10 29

83 5 Northeast 17 Farm Fresh

7 93

84 4 South

Only Pancakes 413 72 9

85 2 South

Red Rock Candy 189 16 71

10 Northeast

Big Waffle 268 2 81

87 8 West

Red Hot Chili Peppers 233 67 64

88 6 Midwest

Meat B Gone 495 61 83

89 11 Northeast

PearApple

73 24

90 10 Midwest

Nap Be Gone 500 56 60

91 1 West

Only Pancakes 413 77 32

92 5 South 8 Red Rock Candy 189 26 50
93 6 West

Orange Creepies 300 5 52

94 9 South 160 Crocodile Tears 143 50 94
95 8 West

Big Waffle 268 22 36

96 10 Midwest

Organiks 257 96 68

97 3 South

PearApple 363 27 88

98 6 Northeast

Mr Greens 447 80 52

99 6 South

Meat B Gone 495 93 90

5 Northeast

Red Rock Candy 189 18 92

4 South

Just The Fruit 160 52 80

2 South

Green Lightning 161 95 74

12 South

Orange Creepies 300 7 28

104 6 South

Crocodile Tears 143 74 86

105 3 West

Only Pancakes 413 63 81

3 Midwest

Nap Be Gone 500 70 3

12 South

Panda Gummies 104 57 74

1 South

Crocodile Tears 143 97 57

109 10 West

Giant Gummies 347 98 52

5 Northeast

Big Waffle 268 57 76

1 Midwest

Organiks 257 68 39

10 Northeast

Farm Fresh 365 4 31

12 Midwest

Yellow Zonkers 134 45 92

2 West

Just The Fruit 160 98 24

11 Northeast 681 Panda Gummies 104 31 94

116 5 Northeast

Yellow Zonkers 134 91 28

8 West 576 Purple Pain 105 43 51

3 South 88 Big Waffle 268 57 74

1 West

Farm Fresh 365 40 35

6 Midwest

Just The Fruit 160 13 44

6 Northeast

Panda Gummies 104 40 51

122 10 West

Panda Gummies 104 79 15

10 South

Panda Gummies 104 99 93

124 6 West

Red Hot Chili Peppers 233 12 94

1 Midwest

Orange Creepies 300 83 79

12 South

Nap Be Gone 500 67 41

6 Midwest

Big Waffle 268 19 66

4 Midwest

Giant Gummies 347 64 94

5 West

Farm Fresh 365 28 81

3 South 624 Crocodile Tears 143 39 86

5 Midwest 638 Only Pancakes 413 59 72

9 South

Only Pancakes 413 48 50

4 Midwest

Yellow Zonkers 134 21 99

134 9 Northeast

Only Pancakes 413 57 73

11 Midwest 795 Power Gels 193 5 36

11 Midwest

Only Pancakes 413 94 97

9 West

Ultra-Salty Chips 454 80 9

1 South

Nap Be Gone 500 65 30

3 South

Purple Pain 105 12 6

10 South

Just The Fruit 160 67 46

12 West

Yellow Zonkers 134 53 85

5 South 103 Grey Gummies 446 45 5

143 5 West

Cheezos 372 12 4

4 South

Crocodile Tears 143 2 100

2 West

Just The Fruit 160 70 34

7 Midwest 734 Ultra-Salty Chips 454 28 76

4 West 7 Power Gels 193 23 54

12 Midwest

Orange Creepies 300 17 46

9 West

Future Toast

78 5

12 West

Ultra-Salty Chips 454 25 72

5 South

Purple Pain 105 89 36

4 West

Red Rock Candy 189 62 46

5 South 234 Power Gels 193 98 93

154 3 Northeast

Grey Gummies 446 24 95

10 South

GMO Guardian 176 43 72

11 South

Farm Fresh 365 4 98

8 West 253 Cheezos 372 14 89

11 West

Rotpunkt 220 8 50

1 South

Crocodile Tears 143 87 51

160 2 West 234 Giant Gummies 347 48 7
161 3 Midwest

Funky Pops 380 75 15

2 South

Crocodile Tears 143 59 45

6 Midwest 114 Yellow Zonkers 134 70 73

5 South

Green Lightning 161 14 71

10 West 128 Funky Pops 380 63 42

5 West

Organiks 257 84 90

11 South

Funky Pops 380 76 58

7 West

Big Waffle 268 82 48

169 12 South 37 Organiks 257 83 47

12 Northeast

Ultra-Salty Chips 454 12 3

4 West

Meat B Gone 495 35 13

1 South

Big Waffle 268 9 81

173 5 South 754 Green Lightning 161 38 9

3 South 155 Big Waffle 268 64 99

12 South

Giant Gummies 347 34 74

176 8 Northeast

Green Lightning 161 75 15

7 South

Future Toast 253 75 98

2 Northeast 120 Mr Greens 447 95 37

11 West

Yellow Zonkers 134 89 57

2 Midwest

Grey Gummies 446 73 26

1 West

Organiks 257 29 47

11 West

Panda Gummies 104 81 76

183 2 West

PearApple 363 2 58

9 South 784 Meat B Gone 495 17 42

3 Northeast 193 Giant Gummies 347 36 86

1 Midwest

Orange Creepies 300 98 54

7 Northeast

Power Gels 193 49 82

8 West

Meat B Gone 495 21 44

189 5 South 348 Grey Gummies 446 17 51
190 5 South

Red Hot Chili Peppers 233 20 25

4 Midwest

Orange Creepies 300 71 58

10 West

Panda Gummies 104 59 42

193 10 Midwest

Just The Fruit 160 31 100

194 8 West 380 Green Lightning 161 4 28

9 South

Power Gels 193 47 93

3 West

Future Toast 253 38 69

2 West

Red Hot Chili Peppers 233 17 40

3 Midwest

Green Lightning 161 92 22

1 Northeast

Big Waffle 268 48 64

8 West

Grey Gummies 446 76 16

10 South

Big Waffle 268 53 35

6 South

Power Gels 193 97 22

203 10 South 624 Only Pancakes 413 88 43
204 4 Midwest

Organiks 257 45 52

205 11 West

PearApple 363 54 55

3 South

PearApple 363 82 99

4 South

Big Waffle 268 58 40

6 Northeast

Big Waffle 268 49 36

8 West

Purple Pain 105 76 92

9 South

Mr Greens 447 61 23

10 West

Orange Creepies 300 42 38

9 Midwest

Ultra-Salty Chips 454 74 49

213 9 Midwest 19 Mr Greens 447 79 31

5 South 187 Red Rock Candy 189 93 63

215 4 West 535 Giant Gummies 347 82 10

7 South

Giant Gummies 347 69 81

2 South 962 Green Lightning 161 56 49

218 11 Midwest 145 Mr Greens 447 95 47

7 West

Organiks 257 75 47

220 5 South 79 Cheezos 372 27 58
221 2 Northeast 643 Power Gels 193 37 68
222 5 Northeast

Nap Be Gone 500 51 68

223 10 Midwest 5 Cheezos 372 35 93

12 West

Giant Gummies 347 38 7

9 Northeast

Farm Fresh 365 98 78

12 West

Only Pancakes 413 17 28

6 South 1 Red Rock Candy 189 2 84

11 South 15 Only Pancakes 413 14 47

1 Midwest 548 Mr Greens 447 14 17

5 South 189 Green Lightning 161 99 46

10 Midwest

Green Lightning 161 94 61

11 South

Future Toast 253 48 17

233 2 South 721 Just The Fruit 160 96 48
234 9 South 143 Nap Be Gone 500 77 99

11 Northeast 129 Power Gels 193 34 77

4 Midwest

Grey Gummies 446 21 19

6 Northeast

Crocodile Tears 143 16 31

238 4 South

Panda Gummies 104 70 68

2 Northeast

Panda Gummies 104 20 35

6 South

Red Hot Chili Peppers 233 96 66

2 South

Farm Fresh 365 37 24

4 Northeast 347 Rotpunkt 220 85 63

9 West

Grey Gummies 446 72 52

244 1 South

Mr Greens 447 83 33

12 West

Just The Fruit 160 75 81

246 8 Northeast 49 Yellow Zonkers 134 16 82

11 West

Organiks 257 93 39

12 South

Green Lightning 161 1 90

3 West 172 Red Rock Candy 189 47 51

12 Midwest 9 Rotpunkt 220 28 27

9 South 36 Only Pancakes 413 18 6

9 Northeast 576 Power Gels 193 18 15

253 9 Midwest

Power Gels 193 43 61

1 Midwest

Mr Greens 447 99 85

3 Midwest 152 Future Toast 253 81 79

256 12 Midwest

Power Gels 193 87 38

257 1 Midwest

Purple Pain 105 52 79

4 West

Big Waffle 268 73 61

10 West 190 Red Hot Chili Peppers 233 85 30

6 Midwest

Yellow Zonkers 134 22 73

9 South 91 Future Toast 253 14 25

10 Northeast

Only Pancakes 413 20 43

6 South 406 Organiks 257 24 64

6 West 174 Big Waffle 268 21 48

265 8 West 165 Ultra-Salty Chips 454 35 49

5 Northeast

Green Lightning 161 97 8

267 11 South 360 Funky Pops 380 48 85
268 11 Midwest 645 Nap Be Gone 500 48 87
269 10 Midwest 114 PearApple 363 62 42

11 Northeast

Farm Fresh 365 90 33

10 South 985 Green Lightning 161 54 69

1 South

Red Hot Chili Peppers 233 92 67

8 South 146 Just The Fruit 160 97 11

10 West

Red Hot Chili Peppers 233 7 74

12 South

Panda Gummies 104 70 47

276 11 Northeast

Panda Gummies 104 21 80

277 10 Midwest 754 Crocodile Tears 143 49 20

4 West 40 Rotpunkt 220 92 71

3 West

PearApple 363 50 7

12 Northeast

PearApple 363 86 50

5 Midwest 191 Grey Gummies 446 43 86

282 1 South 748 Crocodile Tears 143 89 58

2 West

Rotpunkt 220 7 60

2 Northeast

Ultra-Salty Chips 454 90 26

1 Midwest

Red Hot Chili Peppers 233 52 42

12 South 305 Ultra-Salty Chips 454 68 83

12 West 260 Crocodile Tears 143 18 43

288 2 Northeast

Meat B Gone 495 63 65

5 West 421 Green Lightning 161 20 91

290 11 Northeast

Giant Gummies 347 39 63

4 West

GMO Guardian 176 9 54

292 12 West

Power Gels 193 7 42

2 South 519 Just The Fruit 160 23 14

6 South

Organiks 257 22 12

6 South

Power Gels 193 60 58

5 Midwest

Yellow Zonkers 134 14 38

4 West

Green Lightning 161 45 64

6 Midwest 578 Organiks 257 12 44

7 Midwest

GMO Guardian 176 66 66

300 7 Midwest 229 Grey Gummies 446 42 92
301 7 Midwest 196 Crocodile Tears 143 30 29
302 8 South

Farm Fresh 365 22 81

9 South

PearApple 363 55 74

5 Midwest

Organiks 257 56 100

305 3 West

Only Pancakes 413 26 3

4 West 290 Organiks 257 79 65

12 West

Just The Fruit 160 60 27

308 11 South 132 Farm Fresh 365 71 25

5 Northeast 259 Funky Pops 380 46 81

6 West

Just The Fruit 160 66 55

5 Midwest

Red Rock Candy 189 49 93

6 South 286 Purple Pain 105 48 33

313 11 South

GMO Guardian 176 48 87

10 West

Purple Pain 105 24 74

315 5 Northeast 152 Nap Be Gone 500 12 72

7 West 584 Meat B Gone 495 63 93

6 South

Mr Greens 447 35 79

4 South

PearApple 363 23 37

6 Northeast

Grey Gummies 446 78 98

320 9 South 340 Panda Gummies 104 84 29

5 Midwest 211 Rotpunkt 220 42 42

3 West

Mr Greens 447 25 44

323 3 Northeast

Grey Gummies 446 99 7

9 South 748 Grey Gummies 446 66 92

12 West

Cheezos 372 59 82

7 Midwest 656 Rotpunkt 220 8 44

12 South

Organiks 257 28 34

3 Midwest 323 Yellow Zonkers 134 89 47

329 4 Northeast

Farm Fresh 365 66 10

12 South

Power Gels 193 33 65

12 Midwest 294 Purple Pain 105 68 12

11 West

Red Hot Chili Peppers 233 86 86

7 South

Red Rock Candy 189 9 66

3 Midwest

Farm Fresh 365 30 50

335 6 West 52 Orange Creepies 300 30 93

5 West

Nap Be Gone 500 39 28

1 West 416 Purple Pain 105 95 64

6 Northeast 204 Yellow Zonkers 134 61 1

339 7 Northeast 106 Ultra-Salty Chips 454 36 78
340 10 Midwest

GMO Guardian 176 21 33

10 South 328 Just The Fruit 160 20 79

7 South

Cheezos 372 59 67

8 West 117 Only Pancakes 413 27 92

8 West 173 Grey Gummies 446 7 49

12 West 95 Funky Pops 380 38 32

5 South 38 Crocodile Tears 143 39 81

347 2 Northeast 337 Nap Be Gone 500 5 25
348 3 Midwest 734 Green Lightning 161 42 77

8 Midwest

Future Toast 253 49 33

6 South 69 Funky Pops 380 14 91

7 South 397 Cheezos 372 54 35

9 West

Farm Fresh 365 15 39

353 9 West

Orange Creepies 300 60 8

9 Northeast 89 Funky Pops 380 96 56

7 West 154 PearApple 363 33 55

9 Midwest

Panda Gummies 104 48 78

357 9 South 142 Panda Gummies 104 95 5
358 11 Northeast 83 Future Toast 253 51 71
359 2 Northeast 84 Farm Fresh 365 9 23
360 8 Northeast 29 Orange Creepies 300 62 84

8 Northeast

Green Lightning 161 38 88

11 Midwest 16 Organiks 257 33 96

363 1 South 493 Green Lightning 161 30 28
364 1 Midwest 856 Funky Pops 380 14 15
365 1 Midwest 775 GMO Guardian 176 69 26

3 West 249 Giant Gummies 347 80 52

1 Northeast

Ultra-Salty Chips 454 28 53

10 South 372 Red Hot Chili Peppers 233 58 97

11 South 567 Funky Pops 380 83 45

6 South

Panda Gummies 104 92 13

6 South

Big Waffle 268 43 35

372 7 West 320 Giant Gummies 347 29 11

1 South

Red Hot Chili Peppers 233 80 73

2 West

Grey Gummies 446 7 76

9 West 25 Grey Gummies 446 21 43

376 3 South

Funky Pops 380 67 16

4 South 226 PearApple 363 63 39

10 South 226 Just The Fruit 160 83 86

1 South 197 Purple Pain 105 40 18

380 8 Northeast

Organiks 257 67 84

4 Midwest 431 Meat B Gone 495 63 11

7 South

Meat B Gone 495 79 46

1 Northeast

Organiks 257 47 68

8 Northeast 732 Future Toast 253 72 13

8 West 215 Green Lightning 161 50 73

9 South

Farm Fresh 365 97 98

10 South

Ultra-Salty Chips 454 48 70

5 Midwest

Giant Gummies 347 21 96

389 10 Midwest

Only Pancakes 413 86 55

7 Midwest

Mr Greens 447 78 10

9 Midwest 201 Yellow Zonkers 134 10 52

6 Midwest

Purple Pain 105 92 33

11 South 286 Ultra-Salty Chips 454 4 97

394 6 South

Power Gels 193 74 29

8 Midwest

Grey Gummies 446 42 96

1 Midwest

Giant Gummies 347 84 96

397 8 Midwest

Grey Gummies 446 37 93

398 3 West 164 Red Hot Chili Peppers 233 8 30

4 West 250 Grey Gummies 446 98 64

3 West

Purple Pain 105 21 81

401 3 Midwest 307 Red Rock Candy 189 48 45

4 West 288 Meat B Gone 495 93 25

9 South 62 Nap Be Gone 500 62 47

11 West 967 Power Gels 193 8 54

405 8 South 142 Funky Pops 380 73 85
406 12 Northeast 104 Funky Pops 380 46 81
407 2 South 242 Ultra-Salty Chips 454 51 63

11 South

Funky Pops 380 75 12

409 6 Midwest

Yellow Zonkers 134 67 19

410 3 Northeast

Panda Gummies 104 18 15

411 11 South

Future Toast 253 46 85

6 Midwest 507 Meat B Gone 495 43 15

413 11 Midwest

Giant Gummies 347 63 63

3 South

Rotpunkt 220 90 71

9 Midwest 341 Red Rock Candy 189 76 30

416 7 Northeast 103 Red Rock Candy 189 77 77
417 12 West 89 Yellow Zonkers 134 81 91

5 South

Cheezos 372 86 63

10 South 457 Orange Creepies 300 40 91

420 12 South

Purple Pain 105 68 86

421 12 Midwest

Giant Gummies 347 82 97

3 Midwest 309 Nap Be Gone 500 100 33

4 West 856 Big Waffle 268 57 87

10 Midwest 256 Mr Greens 447 78 63

9 South 193 Orange Creepies 300 43 54

8 Northeast 958 PearApple 363 31 72

10 Midwest 78 Ultra-Salty Chips 454 94 46

428 6 West 730 Farm Fresh 365 100 50

9 South

Mr Greens 447 73 15

1 Midwest 31 Crocodile Tears 143 31 70

431 5 Midwest 32 Mr Greens 447 28 72

4 Northeast 274 Big Waffle 268 26 77

433 7 Northeast 359 Big Waffle 268 85 72
434 3 Midwest 965 Cheezos 372 76 36
435 6 Northeast

Green Lightning 161 11 19

11 South 70 Yellow Zonkers 134 42 56

6 Midwest 252 Red Hot Chili Peppers 233 42 32

3 South 51 Purple Pain 105 87 56

439 11 South 59 Red Rock Candy 189 53 78

8 Midwest

Crocodile Tears 143 2 8

11 Northeast 304 Future Toast 253 65 12

3 West 282 Red Rock Candy 189 19 24

443 11 South 279 Future Toast 253 2 43
444 4 Northeast

Mr Greens 447 48 99

1 Northeast 454 Crocodile Tears 143 47 57

446 11 Midwest 406 GMO Guardian 176 77 25
447 3 Northeast 317 PearApple 363 47 20

3 South 258 Purple Pain 105 88 29

7 South 616 Power Gels 193 52 91

450 2 Midwest

Green Lightning 161 30 3

6 West 180 Big Waffle 268 53 67

5 West

Cheezos 372 95 82

1 South 231 Nap Be Gone 500 5 46

454 5 Midwest

Cheezos 372 5 22

3 Midwest 945 Yellow Zonkers 134 84 91

11 South

Orange Creepies 300 50 11

457 3 South 896 Organiks 257 43 94
458 6 Midwest

Red Hot Chili Peppers 233 98 82

459 6 Northeast

Nap Be Gone 500 88 51

3 South

Purple Pain 105 61 77

12 Northeast 244 Yellow Zonkers 134 93 34

462 11 Midwest 15 Future Toast 253 35 25

2 Northeast 761 Organiks 257 62 19

9 Northeast 345 PearApple 363 89 45

5 South 937 Green Lightning 161 17 24

1 West 387 Crocodile Tears 143 54 62

10 West 361 Orange Creepies 300 11 21

468 9 South 11 Giant Gummies 347 59 33

1 West 346 Big Waffle 268 38 75

11 South 408 Rotpunkt 220 17 100

471 8 South 257 Power Gels 193 58 73

1 Midwest 781 Yellow Zonkers 134 59 100

4 South

Just The Fruit 160 87 47

8 Midwest 849 Mr Greens 447 55 37

4 Midwest

Big Waffle 268 97 70

476 7 South 413 Crocodile Tears 143 36 93

5 Northeast

Orange Creepies 300 61 41

12 South 258 Farm Fresh 365 58 29

8 South 314 Just The Fruit 160 61 20

480 5 West

Green Lightning 161 52 20

481 6 South

Giant Gummies 347 90 95

10 Midwest 98 Meat B Gone 495 88 96

483 8 South

Meat B Gone 495 94 70

6 Midwest

Mr Greens 447 98 22

2 Midwest 72 Crocodile Tears 143 91 75

5 Northeast 567 Red Hot Chili Peppers 233 88 42

11 South

Nap Be Gone 500 59 76

7 South 643 Cheezos 372 71 42

8 South 154 Crocodile Tears 143 24 25

12 South 958 Meat B Gone 495 29 99

491 12 Northeast 781 Red Hot Chili Peppers 233 28 35

9 South 601 Nap Be Gone 500 1 17

493 1 Midwest 485 Only Pancakes 413 38 29

12 Midwest

Giant Gummies 347 70 25

495 9 South 148 Panda Gummies 104 21 32

6 West

Giant Gummies 347 94 6

497 12 Midwest 467 Funky Pops 380 65 48
498 9 South 298 Panda Gummies 104 39 20

7 West 73 Panda Gummies 104 25 15

	Month	Region	Customer_ID	Product	Per-Unit Price	Quantity_																																									1	Quantity_																			2
0																																																																													Midwest	2			8	PearApple				3						6	2	5	9
																																																																									West	5						4		Orange Creepies	30	8				7		79
																																																																Northeast				13			Future Toast					25			90
9															20																	Farm Fresh											36											33					74
																																																								12																													South		35		Rotpunkt		22
8								55										GMO Guardian													17													45
4														99																								Mr Greens									44										37			95
1												72																						Just The Fruit									16									84						39
6							18																				Funky Pops															38												28
																																																			11											Giant Gummies				34				98					76
																																										10	4								58										Organiks	2			57				66			68
3													50		3									80																63
												97	4														47						23
			234																							Crocodile Tears										14									64
																			48	1												43														15
5												54																				347								26								56
												91		1							60										75
4																	93																	Red Rock Candy	1										89										40
									32																					160
								19	480																			Power Gels																					193									21
	409																	380
				41													Cheezos													372
1									62																	Nap Be Gone																	500													92							52
																		Only Pancakes																			413
									24											82																		189
																	Meat B Gone												49													67
7												88																				Panda Gummies																					104						29
					27																				Grey Gummies		4										46												73
	218
821																							Green Lightning	1												61
936																				Purple Pain																			105
								31		315																				Big Waffle																				268
	124
8									83																		Ultra-Salty Chips																		454
	329											59
		624																				446									85
	795												220											96
											77																495
		288																				Yellow Zonkers																			134
		416
6											51
											87
																	42		468
		643
	339																							447
525
512
542
7												70
676
809	3							65
											71
					53	511
			406
		431																							143
802							69
	269
739												94
644
	302
565																81
	109										176
	277
	434
815
	638
		397
	169
	681
930																		Red Hot Chili Peppers																	233
591
		748
	122
		348																	300
	116
510
										78		481
873																							161
	483
																						257
531
																	365
		173
580
											86		985
	645
869
		215																363
765
707
		567
	183
	358
913
594
		190
							100		498
101			576
102		353
		103		962
	965
534
	106			754
107			958
108	890
	584
110			734
111	561
112		428
113		238
		114			340
115
678
	117
118
119		223
	120		357
121	657
	301
123	862
618
125	688
126	516
127		784
	128		376
	129	839
130
131
	132		578
133	738
708
135
136				154
137		222
138	610
139	872
140		221
141		313
		142
863
144			256
	145	720
	146
147
	148		775
149		896															253
150	783
151	513
		152	725
153
	491
	155	782
156		417
157
158		292
159	818
778
162	504
163
	164		246
	165
166	970
167			360
168		730
170			282
171		205
	172		308
	174
175	742
		323
177	573
178
179		194
	180			204
181		761
182	555
680
184
185
186	857
	187	860
188		548
	401
	191	968
192	866
	433
195	772
	196		203
	197	696
198		616
199			457
200		535
	201			244
202	710
	213
	364
206	508
207	929
208			359
209	669
210			421
	211		439
212	632
214
216		519
217
219		849
881
224		458
225		335
		226	585
227
228
	229
230
	231		721
232		276
235
236	727
237		945
817
239		267
240	520
241	714
	242
243	545
	265
245	549
247			305
248	619
	249
	250
251
	252
893
254	505
255
	420
		320
		258	653
	259
	260		389
261
262			856
263
264
266		405
270	574
271
272			493
273
	274		967
275	992
865
278
	279			290
280	994
281
283	907
284		656
285	740
		286
287
	471
289
	407
291	583
940
293
	294	623
295		476
296	747
297	647
	298
299		462
	459
303		450
	304		435
953
306
	307	952
	309
310	951
311		444
312
745
	314	847
316
	317	900
318	902
319	986
321
322	698
552
324
325		394
326
327	990
	328
	732
330	924
331
332	894
333	776
334	639
336	910
	337
338
602
	341
342	803
343
344
	345
	346
349	592
350
351
352	763
919
354
355
356		410
	361	933
362
366
367	831
368
369
370		398
371	529
373		507
374	518
375
901
377
378
379
	411
381
382	904
383	685
384
385
386	728
	387	877
388	699
539
390	834
391
392	934
393
774
395	662
396	524
948
399
400	832
402
403
404
	408	880
		781
598
908
412
830
414	801
415
418	836
419
546
850
422
423
424
425
426
427
429		443
430
432
587
436
437
438
440		937
441
442
971
445
448
449
976
451
452	897
453
694
455
456	609
	497
995
460	556
461
463
464
465
466
	467
469
470
472
473	950
474
475	668
477	733
478
479
964
875
482
650
484		601
	485
486
487	659
488
489
490
492
494	769
496	991
499

Additional clarification regarding Week 6 ETL Candy Assignment.

The difficult part about this week’s work is not necessarily the SQL, it’s the Extract-Transform-Load part.

First a few general comments on INSERT INTO and populating a database:

INSERT INTO destinationTable(“x”,”y”,”z”)

SELECT a,b,c FROM sourceTable;

Is different from

INSERT INTO desinationTable (“x”,”y”,”z”)

VALUES (1,2,3);

The latter defines and inserts the VALUES on the spot. But in both the order is extremely important.

In the former and certainly when conducting ETL, the two tables, source and destination may not have the same column names, but it is essential that in the statement that you carefully choose the destinationTable column names for INSERT INTO and the sourceTable for SELECT so they match. But choosing x,y,z for INSERT INTO and then a,b,c for SELECT is the same as INSERT INTO z,y,x with SELECT c,b,a.

Also, as is the case in this Candy assignment, the data itself may need to be manipulated/multiplied/added/etc in some way to get it into a standard form across years.

For example, the following gets FirstName and LastName directly from sourceTable. There is NO SUCH field as AnnualSalary in sourceTable, however there is a MonthlySalary. So the code then multiplies MonthlySalary by 12 to get AnnualSalary. Here you have to be careful about the commas. Until the first comma, FirstName and LastName are being INSERTed INTO FirstName and LastName respectively, after the second comma, the calculation is being INSERTed INTO AnnualSalary.

After AnnualSalary is calculated from “MonthlySalary”, we see that there are two values for bonus pay, Bonus1 and Bonus2. We can calculate TotalBonusPay by adding the two values together in the SELECT statement.

CREATE TABLE destinationTable (FirstName TEXT, LastName TEXT, AnnualSalary INTEGER, TotalBonusPay INTEGER);

INSERT INTO destinationTable(“FirstName”,”LastName”,”AnnualSalary”,”TotalBonusPay”)

SELECT “FirstName”,”LastName”,”MonthlySalary” * 12, “Bonus1” + “Bonus2” FROM sourceTable;

Now let’s look specifically at our Candy dataset.

If we had ‘perfect’ data, an INSERT INTO and SELECT statement to populate stagingTable might look something like this, 11 fields in stagingTable from 11 fields in pd2017, all nicely arranged with the same names, same values. However, note they sometimes use different field names:

stagingTable uses Product_Name and unitPrice, while pd2017 uses Product and Per-Unit_Price.

What’s important here is that the field names for INSERT INTO correspond to stagingTable (where we’re inserting the data) and SELECT correspond to pd2017 (or pd2018 or pd2019).

INSERT INTO stagingTable(“monthInt”, “state”, “country”, “region”, “Product_Name”, “unitPrice” –you need to fill this in here)

SELECT “Month”, “State”, “Country”, “Region”, “Product”, “Per-Unit_price”, “Quantity”, “Order_Total” FROM pd2017;

HOWEVER, rarely does data comes in a neat format. So you first need to look at the data (you can do this in Excel). Start with 2017 and you’ll see the following columns, not 11 fields, only 8:

2017:

2018:

2019:

You should note how the fields/columns changed over the years. Attention: the following list is NOT complete. Important information is given in each year’s Data Notes (part c) and conduct your own analysis of the differences in data. This is the heart of the ETL process and you will document this in your one page write up as a part of this assignment.

Now look at the stagingTable structure:

Each year MUST conform to this stagingTable structure so that all three years can be analyzed.

· However, we’re not going to ‘make up’ customer_id where there is none.

· Also don’t worry about adding data to country where it might be listed as ‘null’.

· The most important fields that must be properly populated in stagingTable for proper analysis are quantity and orderTotal.

2017 will be imported relatively straightforward via an INSERT INTO and SELECT command.

2018 and 2019, however, will require you to choose the correct fields (from stagingTable) in the INSERT INTO command and then SELECT fields FROM pd2018 or pd2019 which will sometimes require some basic math to ‘transform’ the data into a common, usable format.

Again, this ‘Additional Clarification’ does not describe ALL of the differences in data that you will need to address. Properly identifying differences and discrepancies and transforming the data into a usable format is essential to the ETL task.

Finally, you’re not making up any data, just transforming what is there. Also note that ‘year’ is not an element in any of the individual year table, which is why you need an UPDATE command after your INSERT INTO and SELECT command after each year. This for 2018:

UPDATE stagingTable SET yearInt=2018 WHERE yearInt ISNULL;

–Skeleton Candy Part 1 – extract from 2017, 2018, 2019 tables and put into stagingTable
–Students, see notes below where it says –you need to fill this in here
–Create the staging table
DROP TABLE IF EXISTS stagingTable;
CREATE TABLE stagingTable (
yearInt INT(4),
monthInt INT(2),
–there will be more you need to fill in here
);

–Insert 2017 Data
INSERT INTO stagingTable(“monthInt”, “state”, “country”, “region”, “Product_Name”, “unitPrice” –you need to fill this in here)
SELECT “Month”, “State”, “Country”, “Region”, “Product”, “Per-Unit_price”, “Quantity”, “Order_Total” FROM pd2017
;
UPDATE stagingTable SET yearInt=2017;
–Insert 2018 Data
INSERT INTO stagingTable( — you need to fill this in here –)
SELECT … FROM pd2018
;
UPDATE stagingTable SET yearInt=2018 WHERE yearInt ISNULL;

–Insert 2019 Data
INSERT INTO stagingTable(– you need to fill this in here –)
SELECT … FROM pd2019
;
UPDATE stagingTable ( — you need to fill this in here );

SELECT * FROM stagingTable;

UMGCIFSM 330 Candy Assignment

Attachments:

· 2017_product_data_students-final.csv

· 2018_product_data_students-final.csv

· 2019_product_data_students-final.csv

· Candy_part_1_skeleton_for_students.SQL

In this assignment, you will produce SQL code which scrubs and imports each of the three years’ worth of data, and produces an output file called stagingTable.

Along with these instructions, there is another document, ‘Additional Clarification on the Week 6 Candy Assignment’. Please read that document carefully.

Let’s get started!

Part A: Upload all the files you will need to SQLlite:

1. Import the file called “2017_product_data_students.csv” to SQLiteonline.com. When you import it, give it the table name “pd2017” (no quotes) and set the column name to “First line.”

2. Import “2018_product_data_students.csv” as “pd2018”

3. Import “2019_product_data_students.csv” as “pd2019”

Part B: Extract and Transform your data

Your job is to use SQL to perform an ETL which will accomplish the following:

1. Start with the skeleton starter script we give you, attached to this assignment. Modify the CREATE TABLE command so the schema is as follows:

Computer code

Notes about what you need to do

DROP TABLE IF EXISTS stagingTable

;

Leave this code alone – it ensures you a fresh start

CREATE TABLE stagingTable (

yearInt INT(4),

monthInt INT(2),

–there will be more you need to fill in here

);

Right now this creates stagingTable with only two fields, the yearInt and monthInt. Modify the code where highlighted in yellow to correspond to the schema below.

And of course, when you’re done coding, remove the ‘– there will be more you need to fill in here’ comment

2. Get the 2017 bit of the script working.

Computer code

Notes about what you need to do

–Insert 2017 Data	This is a comment telling you the 2017 data is going to be inserted here
INSERT INTO stagingTable(“monthInt”, “state”, “country”, “region”, “Product_Name”, “unitPrice” –you need to fill this in here)	After you have created stagingTable, this is the first half of a command that will insert the data into stagingTable. You need to replace the yellow highlighted material with your own code to complete it.
SELECT “Month”, “State”, “Country”, “Region”, “Product”, “Per-Unit_price”, “Quantity”, “Order_Total” FROM pd2017 ;	This is the second half of a command that will insert data into stagingTable. You don’t need to change anything here.
UPDATE stagingTable SET yearInt=2017;	This sets the year to 2017 for this data. You don’t need to change this.

3. Get the 2018 part of the script working.

Computer code

Notes about what you need to do

–Insert 2018 Data		Comment.
	INSERT INTO stagingTable(–you need to fill this in here)	This is the first half of a command that will insert the data into stagingTable. You need to replace the yellow highlighted material with your own code to complete it. See the rules below for more details.
SELECT … FROM pd2018 ;	This is the second half of a command that will insert data into stagingTable.		You need to replace the yellow highlighted material.
UPDATE stagingTable SET yearInt=2018 WHERE yearInt ISNULL;	This sets the year to 2018 for any new entries which don’t yet have a year. You don’t need to change this.

4. Get the 2019 part of the script working.

Computer code

Notes about what you need to do

Comment.

INSERT INTO stagingTable(–you need to fill this in here)

You need to replace the yellow highlighted material.

–Insert 2019 Data

You need to replace the yellow highlighted material with your own code to complete it. See the rules below for more details.

SELECT … FROM pd2019

;

UPDATE stagingTable …;

5. The script will load it into one final table and call it stagingTable

6. Run the checksum script to verify you have the stagingTable calculated correctly.

You should do this all in SQLlite. You should not export to Excel and do your manipulations in Excel.

Part C: 2017 Data Notes

Table 1 Sample of order data from 2017

. (Note your file may or may not have the same data in it.)

Your field definitions follow:

· Month: integer, corresponds to the month of the sale. For example, 5 = May.

· Country: text, should all be USA. (All data in this exercise should be USA.)

· Region: text, represents the regions within the country.

· State: text, USPS state abbreviations. Each state is within one region.

· Product: text. This is the name of a packaged food product.

· Quantity: integer. This represents how many items were in that particular order. The first order here was for 49 packages of Orange Creepies.

· Order Total: integer. This is the per-unit price x the quantity. The first line here indicates that 300 x 49 = 14700 (or $147.00) was the price of the first order.

Table 1 Sample of order data from 2017

Month

Country

Region

State

Product

Per-Unit Price

Quantity

Order Total

USA

West

Orange Creepies

300

14700

USA

Northeast

Farm Fresh

365

17885

USA

South

Farm Fresh

365

3650

USA

South

Organiks

257

6939

USA

South

PearApple

363

30129

USA

South

Big Waffle

268

1340

2018 Data Notes:

Your order 2018 data is contained in the attached file, “2018_product_data_students.csv”

A sample of this file’s data is contained below as

Table 2 Sample of order data from 2018

. (Note your file may or may not have the same data in it.)
Your field definitions follow:
· Month: integer, corresponds to the month of the sale. For example, 5 = May.
· Region: text, represents the regions within the country.

· Customer_ID: integer, represents the customer’s unique Customer ID number.

· Product: text. This is the name of a packaged food product.

· The first line here reflects that PearApple has a first shipment of 25 units, and a second shipment of 92 unit, all within the month of January, for a total of 25 + 92 = 117 units.

Table 2 Sample of order data from 2018

Month

Region

Customer_ID

Product

Per-Unit Price

Quantity_1

Quantity_2

Midwest

280

PearApple

363

West

545

Orange Creepies

300

Northeast

131

Future Toast

253

Midwest

920

Farm Fresh

365

South

358

Rotpunkt

220

South

855

GMO Guardian

176

2019 Data Notes:

Your order 2019 data is contained in the attached file, “2019_product_data_students.csv.”

A sample of this file’s data is contained below as

Table 3 Sample of order data from 2019

. (Note your file may or may not have the same data in it.)
Your field definitions follow:
· Month: integer, corresponds to the month of the sale. For example, 5 = May.

· Country: text, represents the country of the customer. Should all be USA.

· Region: text, represents the regions within the country.

· State: USPS code for the 50 United States.

· Product: text. Same as previous years.

· Per-unit price: integer. This represents the per-unit price in cents; same as previous years.

· Quantity: This represents how many items were in that particular order. The first order here was for 95 packages of Only Pancakes.

· Order 0, on the first line, of 95 Only Pancakes to Florida, did qualify for the Quantity Discount, because an order quantity of 95 exceeded the 90 threshold. The Quantity Discount has been computed as 3924, or 10% of 39235. In this case, the final order total would be 39,235 – 3,924 = 35,311 (or $353.11).

· Order 4, on the fifth line, of 31 Future Toasts to North Carolina, did not qualify for the Quantity Discount. Therefore, the Order total would simply be the Order subtotal.

Table 3 Sample of order data from 2019

Month

Country

Region

State

Product

Per-Unit Price

Quantity

Order Subtotal

Quantity Discount

USA

South

Only Pancakes

413

39235

3924

USA

West

Big Waffle

268

24924

2492

USA

Northeast

Grey Gummies

446

42370

4237

USA

Midwest

Funky Pops

380

100

38000

3800

USA

South

Future Toast

253

7843

USA

West

Mr Greens

447

33972

USA

South

Giant Gummies

347

32271

3227

Part D: Check Your Own Work

1. You can run the following SQL code on your staging table. There is nothing to turn in from this bit. It should yield the following first few rows:

Select region, yearint, monthInt, count(*) from stagingTable where monthInt = 5 group by region, yearInt, monthInt;

2. You can also run the following code to debug. You should get the following rows:

Select yearInt, monthInt, state, customer_id, product_name, orderTotal from stagingTable

where product_name = ‘Big Waffle’ and monthint=4

order by product_name, yearInt, monthInt, state, customer_id, orderTotal;

select sum(yearInt * monthInt * orderTotal)%2341 as checksum from stagingTable;

3. Once you get the result of your CHECKSUM look at table below for ways to troubleshoot any issues with your ETL statements.

If you get this checksum:

Hint:

1021

No further troubleshooting needed, go ahead, and submit your 3 deliverables

315

Look at your calculation for OrderTotal for 2019

349

Look at your calculation for OrderTotal for 2018 AND 2019

1944

Look at your calculation for OrderTotal for 2018 AND 2019 (hint: subtotal is NOT equal to ordertotal)

1767

Look at how your calculation for the quantity total for 2018.
Look at your calculation for OrderTotal for 2019

953

Look at how your calculation for the quantity total for 2018.
Look at your calculation for OrderTotal for 2018 AND 2019

Other

Look carefully at your syntax for every field. Run your 2017 insert first, then run your 2018 insert, then your 2019 insert statement.

TURN IN:

1. Your output file, called “XX_output_final.csv” where XX are your initials.

2. All the SQL code you used to execute this.

3. A document that contains

a. CHECKSUM: XXX where XXX is the checksum number produced. Put this in big font right on the top.

IFSM 330 Week 6 Candy v04 13

Turn in your highest-quality paper
Get a qualified writer to help you with

“ SQL Candy Assignment ”

Get high-quality paper

NEW! AI matching with writer

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order