Du jobbar för ett företag som säljer en prenumerationstjänst till privatpersoner. Din chef vill ha en Business Intelligence-lösning där alla köp kopplas till ett ”CustomerID” (kundnummer). Problemet är att ni inte har något sådant fält på köpen.
Även om ni har namn, adress och kreditkortsnummer på varje köp så är det inte uppenbart vilka betalningar som kommer från samma kund. En kund kan byta namn, flytta, byta kreditkort, etc. Ni vill ändå försöka skapa ett ”CustomerID” som stämmer så bra som möjligt.
Varje kund köper tjänsten högst en gång per kalendermånad. Du kan alltså utgå från att en kund som köpt tjänsten någon dag i januari inte kommer göra något nytt köp förrän tidigast i februari.
För att identifiera vilka köp som gjorts av samma kund så görs en matchning mellan köpen.
- Om FirstName och CreditCard stämmer så är det en godkänd matchning.
- Om FirstName och Email stämmer så är det en godkänd matchning.
- Om FirstName, LastName, Address och ZipCode stämmer så är det en godkänd matchning.
Om alltså minst ett av ovanstående tre villkor är uppfyllt så antas det vara samma kund som gjort köpen (alltså skall de få samma ”CustomerID”).
Du kan utgå från att matchningarna ovan aldrig ger några falska träffar. Varje godkänd matchning innebär att det måste vara samma ”CustomerID” på köpen.
Matchning skall göras mot kundernas alla tidigare köp. En kund kan exempelvis växla mellan ett par olika kreditkort eller tillfälligt flytta till en adress.
Som indata får du en tabell med köp (exakt tabelldefinition finns i SQL-scriptet nedan).
Tabell: Purchases
| PurchaseID | Date | FirstName | LastName | StreetAddress | ZipCode | CreditCard | |
| 1 | 2016-01-10 | Anders | Andersson | anders@telia.com | Lingonvägen 1 | 12345 | 1111-2222-3333-4444 |
| 2 | 2016-01-15 | Elin | Andersson | elin@yahoo.se | Storgatan 2 | 23456 | 9999-8888-7777-6666 |
| 3 | 2016-02-10 | Elin | Andersson | elin@mail.com | Lillgatan 2 | 34567 | 9999-8888-7777-6666 |
| 4 | 2016-02-11 | Anders | Andersson | anders@telia.com | Lingonvägen 1 | 12345 | 4444-4444-4444-4444 |
| 5 | 2016-04-02 | Anders | Andersson | anders@hotmail.com | Blåbärsvägen 1 | 54321 | 1111-2222-3333-4444 |
| 6 | 2016-04-05 | Nils | Andersson | nils@hotmail.com | Blåbärsvägen 1 | 54321 | 1111-2222-3333-4444 |
Du kan utgå från att PurchaseID är i kronologisk ordning.
Tabell: Customers
Resultatet skall skrivas till tabellen ”Customers”. Din lösning skall fylla på den med en rad för varje köp.
| PurchaseID | CustomerID |
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
| 6 | 3 |
Det finns inga särskilda krav på numreringen av CustomerID. Det enda viktiga är att det skall gå att använda CustomerID för att knyta ihop alla köp som kommer från samma kund.
Kom igång
SQL-script för att skapa tabellerna och en mindre mängd testdata finns här.
Vi rekommenderar att du skapar en större mängd testdata.
Tävlingsansvariga
Tävlingen arrangeras av Peter Larsson och Johan Åhlén. Uppgiften är konstruerad av Peter Larsson.
Utvärdering
Vinnare är det tävlingsbidrag som ger rätt resultat och är snabbast, d v s har lägst exekveringstid (”duration”). Högsta tillåtna antal tävlingsbidrag per deltagare är tre.
Tävlingsbidragen testkörs på en Azure SQL databas på tjänstenivå P1.
Obs! När tävlingsbidragen utvärderas så används betydligt större mängder testdata än exemplet du laddar ned här på sidan. Dessutom kan mängden testdata ökas ytterligare för att göra tävlingen utslagsgivande.
sqlug.se 