At the beginning of the year, a senior manager created a scoring system to

set individual and team objectives appropiately. It is now time to review

the annual results.
easier and/or automated than an excel tool or function.
Reference page is here to give you additional information to use, it doesn't mean you don't have the
right to rearrange the format if you need it. Be creative so that your final solution
is as automated and scalable as possible
 Opportunity ID Merchant Name
00630000008PR62 Centralcrafts
0063000000AVIF6 Firstaid 4 Sport
0063000000C69gU John Bull Antiques [email protected]
0063000000C8RPx Pool Tables Online
0063000000CbIRs Cricket Hockey Direct
0063000000CZJaQ Krypton Komics
0063000000EdW33 the-memorabilia-shop
0063000000HNlHw Aire Guitars
0063000000HOER2 ish-global.com
0063000000HP59n Cherlyn Electronics Limited
0063000000IFkjX hiking-and-hobbies
0063000000IGm2j Paula Barbacues Limted.
0063000000IIapt Racevisors
0063000000IIcEW Bags Of Fun
0063000000JBXh0 Pro-xsports
0063000000JEPCJ SDS Watersports
0063000000JF1O7 Purely Pet Beds
0063000000JF1TB [email protected],
0063000000JF8vi Secondtek Ltd
0063000000KGWWb Enjoy Your Baby
0063000000KH1nr Motion Performance
0063000000KH2ED jd-collectables
0063000000KHAia Innovative Computers
0063000000KHIip Hi Grade Communications Ltd
0063000000KHx2c Cellustar
0063000000KHXdE The I People
0063000000KIcXd Direct Wallpaper
0063000000KJJEo Select Wallpaper
0063000000KJJGT Collars and Tags Ltd
0063000000KKGpg First Call Catering
0063000000KKQ7i Embrace Air Products
0068000000LjplF Icarus Houseware
0068000000LjS1h VidaHomes
0068000000LjS1Y Pocket Money Princess
0068000000LkATW Razzamatazz Ltd
0068000000LkAWa Your Little Party
0068000000LkS41 Outdoor Leisure Shop
0068000000LtAtj GoldenSABanana ltd
0068000000LYt0Q ArtifiCert LTD
0068000000LZ4Y2 CLEVERBASE LTD
0068000000LZA90 All Records
 Opportunity Name Sales Specialist
[email protected] Jackson Tayler
Firstaid 4 [email protected] Jackson Tayler
John Bull Antiques [email protected] Jackson Tayler
Pool Tables [email protected] Jackson Tayler
Cricket Hockey [email protected] Jackson Tayler
Krypton Komics-UK-PM-BMV Jackson Tayler
[email protected] Jackson Tayler
Air [email protected] Jackson Tayler
ish-global.com-UK-PM-BMV Jackson Tayler
Cherlyn Electronics [email protected] Jackson Tayler
[email protected] Jackson Tayler
Paula Barbacues [email protected] Jackson Tayler
[email protected] Jackson Tayler
Bags Of [email protected] Jackson Tayler
[email protected] Jackson Tayler
SDS [email protected] Jackson Tayler
Purely Pet [email protected] Jackson Tayler
[email protected],[email protected] Jackson Tayler
Secondtek [email protected] Jackson Tayler
Enjoy Your [email protected] Jackson Tayler
Motion [email protected] Jackson Tayler
[email protected] Jackson Tayler
Innovative [email protected] Jackson Tayler
Hi Grade Communications [email protected] Jackson Tayler
[email protected] Jackson Tayler
The I [email protected] Jackson Tayler
Direct [email protected] Jackson Tayler
Select [email protected] Jackson Tayler
Collars and Tags Ltd-HOME-UK-M@1-0 Jackson Tayler
First Call [email protected] Jackson Tayler
Embrace Air [email protected] Jackson Tayler
Icarus [email protected] Jackson Tayler
[email protected] Jackson Tayler
[email protected] Jackson Tayler
Razzamatazz [email protected] Jackson Tayler
Your Little [email protected] Jackson Tayler
Outdoor Leisure [email protected] Jackson Tayler
GoldenSABanana [email protected] Jackson Tayler
ArtifiCert [email protected] Jackson Tayler
CLEVERBASE [email protected] Jackson Tayler
All Records-UK-PM-BMV Jackson Tayler
 Opportunity Record Type Product Group Close Date Listings Category (Q2 Answer here)
M@ UK Home 7/10/2008 814
M@ UK Sporting Goods 8/1/2008 122
M@ UK Home 7/28/2008 416
M@ UK Sporting Goods 8/1/2008 130
M@ UK Sporting Goods 8/18/2008 125
UK Marketplace Comics 8/14/2008 2753
M@ UK Toy, Hobby & Craft 8/19/2008 296
M@ UK Electronics 8/6/2008 56
UK Marketplace Books 8/11/2008 1
M@ UK Electronics 7/7/2008 21
M@ UK Toy, Hobby & Craft 7/15/2008 109
M@ UK Home 7/7/2008 9
M@ UK Sporting Goods 7/30/2008 25
M@ UK Home 7/7/2008 12
M@ UK Sporting Goods 7/21/2008 91
M@ UK Sporting Goods 9/10/2008 105
M@ UK Home 9/2/2008 93
M@ UK Home 7/7/2008 35
M@ UK Home 7/15/2008 11
M@ UK Baby 9/18/2008 3
M@ UK Home 9/12/2008 485
M@ UK Toy, Hobby & Craft 7/7/2008 359
M@ UK Electronics 7/9/2008 32
M@ UK Electronics 7/7/2008 27
M@ UK Electronics 7/29/2008 155
M@ UK Sporting Goods 7/28/2008 64
M@ UK Home 7/2/2008 167
M@ UK Home 7/24/2008 7969
M@ UK Home 7/7/2008 169
M@ UK Home 7/24/2008 149
M@ UK Sporting Goods 7/9/2008 3
M@ UK Toy, Hobby & Craft 8/21/2008 11
M@ UK Home 8/6/2008 157
M@ UK Toy, Hobby & Craft 8/13/2008 105
M@ UK Home 9/8/2008 1872
M@ UK Home 9/1/2008 23
M@ UK Sporting Goods 9/17/2008 148
M@ UK Electronics 9/19/2008 11
M@ UK Electronics 7/17/2008 273
M@ UK Electronics 7/24/2008 21
UK Marketplace Music 9/17/2008 33
Points (Q3 Answer Here)
Listing thresholds and Point system Definitions
Hardlines Tier 0 Tier 1 Tier 2 Listings ; number of products each merchant sells
Listing Thresholds 1-19 20-149 150 - Deal; A signed merchant
Points 0.5 1 2 Points ; credit given to the sales specialist for each signed merchant
Listing thresholds ; range of listings that are each associated with fixed points
Softlines Tier 0 Tier 1 Tier 2 example: for merchants with listings between 1 to 19 the sales specialists
Listing Thresholds 1-19 20-99 100 - are given 0.5 points.
Points 0.5 1 2

Media Tier 0 Tier 1 Tier 2

Listing Thresholds 1-19 20-499 500 -
Points 0.5 1 1.5

Category Table
Product Group Category
Baby Hardlines
Electronics Hardlines
Home Hardlines
Software Hardlines
Tools Hardlines
Toy, Hobby & Craft Hardlines
Video Games Hardlines
Apparel Softlines
Beauty Softlines
Health Softlines
Jewelry Softlines
Shoes Softlines
Sporting Goods Softlines
Watches Softlines
Books Media
Comics Media
DVD Media
Music Media
Q1
Q4

SELECT SALES_SPECIALIST,PRODUCT_GROUP, SUM(POINTS) FROM RAWDATA GROUP BY SALES_SPECIALIST,PRODUCT_GROUP

CIALIST,PRODUCT_GROUP
Q5
SELECT COUNT(OPPORTUNITY_NAME), SUM(LISTINGS ) FROM RAW_dATA WHERE MERCHANT_NAMLE LIKE 'SOFTWARE' GROU
MLE LIKE 'SOFTWARE' GROUP BY MERCHANTNAME
Q6
SELECT SALES_SPECIALIST,PRODUCT_GROUP,SUM(POINTS) FROM RAW_DATA WHERE CATEGORY=HARDLINE GROUP BY SALES
ARDLINE GROUP BY SALES_SPECIALIST,PRODUCT_GROUP
Table A Table B

autor_id book_id customer_id book_id quantity_ordered

295085 b-346991 c-8663000 b-872492 1
295085 b-351881 c-8663000 b-847625 1
295085 b-975654 c-8663000 b-172690 1
295085 b-676305 c-4038000 b-676305 1
269827 b-749886 c-3463000 b-480868 1
921265 b-480868 c-6360000 b-110233 2
921265 b-172690 c-8858000 b-383442 1
827606 b-555058 c-8858000 b-676305 1
327453 b-383442 c-115000 b-508366 4
579724 b-919167 c-8858000 b-555058 1
579724 b-872492 c-8858000 b-480868 2
579724 b-479998 c-5662000 b-847625 1
800347 b-508366 c-5255000 b-480868 1
800347 b-847625 c-1912000 b-676305 1

SQL Q1 - put answer in HireVue: select sales_specialist from rawdata group by sales_specialist having count(1) >40
SQL Q2 - put answer in HireVue: update rawdata r set category = (select category from category where product_group = r.pro
SQL Q3 - put answer in HireVue: update rawdata r set points = case when listings between 1 and 19 then 0.5 when ((category

SELECT A.AUTHOR_ID FROM TABLEA A WHERE A.BOOK_ID NOT IN (SELECT BOOK_ID FROM TABLEB)
SELECT BOOK_ID ,LISTAGG(CUSTOMER_ID,',') FROM TABLEB GROUP BY BOOK_ID
aving count(1) >40
where product_group = r.product_group )
19 then 0.5 when ((category=hardline and listings between 20 and 149) or (category=softline and listings between 20 AND 99) OR (CATEG

D FROM TABLEB)
een 20 AND 99) OR (CATEGORY=MEDIA AND LISTINGS BETWEEN 20 AND 499) THEN 1 ELSE CASE category WHEN hardline THEN 2 WHEN SO
hardline THEN 2 WHEN SOFTLINE THEN 2 WHEN MEDIA THEN 1.5 END END