M-Kopa-mini-challenge
SQL Test From M-kopa
Table of Contents
Datasets
AssessmentCustomers
AssessmentSales
AssessmentDailyLoanInfo
_NB: A loan is created for each sale. This table includes the outstanding balance and the total amount paid for each loan every day after the sale is made.
Question
- Select all the male customers from outside Nairobi.
- Select all the customers with an extra column containing the age they were when they joined. Name this column AgeWhenJoining.
- Using the table above, calculate the average customer age when joining. Use a sub-query, CTE or temporary table.
- Still using the same table, calculate the average, maximum and minimum customer age when joining per gender.
- Select the 100 customers who generated the most sales. Show their MartCustomerId, Town and the number of sales each of them generated.
- How many customers have no sale? Show only the number of customers.
- Select all the AssessmentSales table with an extra column that displays 1 when the ProductSubCategory is 'M-KOPA 4' and 0 for all the other ones. Call it IsMkopa4.
- Select all the AssessmentSales table with an extra column that shows the loan number for each customer: 1 if it's the customer's first sale, 2 if it's the customer's second sale etc. Call it SaleNumber.
- For each customer's age category below (age today), provide the total outstanding balance and the total amount paid in on the 01/08/2018. The results must all be from a single query.
- Under 30 years old
- Between 30 and 49 years old
- Over 50 years old
Answers
Q1. Select all the male customers from outside Nairobi.
SELECT * FROM assessmentcustomers
WHERE Gender = 'Male' AND Town != 'Nairobi';Results
| MartCustomerId | Gender | Town | DateOfBirth | JoiningDate |
|---|---|---|---|---|
| 981683 | Male | Kilifi | 1993-01-01 00:00:00 | 2018-07-05 07:06:40 |
| 955015 | Male | Kakamega | 1992-03-07 00:00:00 | 2018-03-23 11:30:17 |
| 952475 | Male | Meru | 1953-01-01 00:00:00 | 2018-03-14 06:58:08 |
Q2. Select all the customers with an extra column containing the age they were when they joined. Name this column AgeWhenJoining. Select all the customers with an extra column containing the age they were when they joined. Name this column AgeWhenJoining.
SELECT *, YEAR(JoiningDate) - YEAR(DateofBirth) AS AgeWhenJoining
FROM assessmentcustomers;Results
| MartCustomerId | Gender | Town | DateOfBirth | JoiningDate | AgeWhenJoining |
|---|---|---|---|---|---|
| 935816 | Female | Marsabit | 1966-01-01 00:00:00 | 2018-01-13 12:54:53 | 52 |
| 936167 | Female | Migori | 1959-01-01 00:00:00 | 2018-01-15 07:55:46 | 59 |
| 952475 | Male | Meru | 1953-01-01 00:00:00 | 2018-03-14 06:58:08 | 65 |
| 955015 | Male | Kakamega | 1992-03-07 00:00:00 | 2018-03-23 11:30:17 | 26 |
| 963022 | Female | Trans Nzoia | 1980-01-01 00:00:00 | 2018-04-21 07:19:01 | 38 |
| 981683 | Male | Kilifi | 1993-01-01 00:00:00 | 2018-07-05 07:06:40 | 25 |
Q3. Using the table above, calculate the average customer age when joining. Use a sub-query, CTE or temporary table.
WITH customerage AS
(SELECT *, YEAR(JoiningDate) - YEAR(DateofBirth) AS AgeWhenJoining FROM assessmentcustomers)
SELECT AVG(AgeWhenJoining) FROM customerage;Results
| AverageCustomerAge |
|---|
| 44.1667 |
Q4. Still using the same table, calculate the average, maximum and minimum customer age when joining per gender.
WITH customerage AS
(SELECT *, YEAR(JoiningDate) - YEAR(DateofBirth) AS AgeWhenJoining FROM assessmentcustomers)
SELECT Gender, AVG(AgeWhenJoining) AS Average, MAX(AgeWhenJoining) AS Maximum, MIN(AgeWhenJoining) AS Minimum FROM customerage
GROUP BY Gender;Results
| Gender | AverageAge | MaximumAge | MiniumAge |
|---|---|---|---|
| Female | 49 | 59 | 38 |
| Male | 38 | 65 | 25 |
Q5. Select the 100 customers who generated the most sales. Show their MartCustomerId, Town and the number of sales each of them generated.
SELECT assessmentcustomers.MartCustomerId, assessmentcustomers.Town, COUNT(assessmentsales.MartLoanId) AS Numberofsales FROM assessmentcustomers
LEFT JOIN assessmentsales ON assessmentcustomers.MartCustomerId = assessmentsales.MartCustomerId
GROUP BY assessmentcustomers.MartCustomerId
ORDER BY Numberofsales DESC
LIMIT 100;Results
| MartCustomerId | Town | Numberofsales |
|---|---|---|
| 935816 | Marsabit | 0 |
| 936167 | Migori | 0 |
| 952475 | Meru | 0 |
| 955015 | Kakamega | 0 |
| 963022 | Trans Nzoia | 0 |
| 981683 | Kilifi | 0 |
Q6. How many customers have no sale? Show only the number of customers.
SELECT COUNT(MartCustomerId) AS CustomersWithNoSales FROM assessmentcustomers
WHERE NOT EXISTS (SELECT MartCustomerId FROM assessmentsales WHERE assessmentcustomers.MartCustomerId = assessmentsales.MartCustomerId);Results
| CustomersWithNoSales |
|---|
| 6 |
Q7. Select all the AssessmentSales table with an extra column that displays 1 when the ProductSubCategory is 'M-KOPA 4' and 0 for all the other ones. Call it IsMkopa4.
SELECT *,
DENSE_RANK() OVER (PARTITION BY MartCustomerId ORDER BY DateOfSale ASC) AS SaleNumber
FROM assessmentsales;Results
| MartCustomerId | MartLoanId | DateOfSale | ProductCategory | ProductSubCategory | IsMkopa4 |
|---|---|---|---|---|---|
| 939037 | 5040249 | 2018-01-24 00:00:00 | MK Starter | M-KOPA 4 Starter | 0 |
| 953935 | 5603865 | 2018-03-20 00:00:00 | MK TV | M-KOPA 500 | 0 |
| 956589 | 5612579 | 2018-03-29 00:00:00 | MK TV | M-KOPA 500 | 0 |
| 960948 | 5624226 | 2018-04-13 00:00:00 | MK-Classic | M-KOPA 5 | 0 |
| 966788 | 5642449 | 2018-05-08 00:00:00 | MK Clasic | M-KOPA 5 | 0 |
| 953935 | 5704876 | 2018-04-20 00:00:00 | MK TV | M-KOPA 500 | 0 |
| 1000927 | 5742532 | 2018-09-01 00:00:00 | MK TV | M-KOPA 500 | 0 |
| 956589 | 6714976 | 2018-04-29 00:00:00 | MK TV | M-KOPA 500 | 0 |
**Q8. Select all the AssessmentSales table with an extra column that shows the loan number for each customer: 1 if it's the customer's first sale, 2 if it's the customer's second sale etc. #Call it SaleNumber.
SELECT *,
DENSE_RANK() OVER (PARTITION BY MartCustomerId ORDER BY DateOfSale ASC) AS SaleNumber
FROM assessmentsales;Results
| # MartCustomerId | MartLoanId | DateOfSale | ProductCategory | ProductSubCategory | SaleNumber |
|---|---|---|---|---|---|
| 939037 | 5040249 | 2018-01-24 00:00:00 | MK Starter | M-KOPA 4 Starter | 1 |
| 953935 | 5603865 | 2018-03-20 00:00:00 | MK TV | M-KOPA 500 | 1 |
| 953935 | 5704876 | 2018-04-20 00:00:00 | MK TV | M-KOPA 500 | 2 |
| 956589 | 5612579 | 2018-03-29 00:00:00 | MK TV | M-KOPA 500 | 1 |
| 956589 | 6714976 | 2018-04-29 00:00:00 | MK TV | M-KOPA 500 | 2 |
| 960948 | 5624226 | 2018-04-13 00:00:00 | MK-Classic | M-KOPA 5 | 1 |
| 966788 | 5642449 | 2018-05-08 00:00:00 | MK Clasic | M-KOPA 5 | 1 |
| 1000927 | 5742532 | 2018-09-01 00:00:00 | MK TV | M-KOPA 500 | 1 |
Q9. For each customer's age category below (age today), provide the total outstanding balance and the total amount paid in on the 01/08/2018. The results must all be from a single query.
- Under 30 years old
- Between 30 and 49 years old
- Over 50 years old
SELECT
(CASE
WHEN YEAR(NOW()) - YEAR(DateOfBirth) < 30 THEN 'UnderThirty'
WHEN YEAR(NOW()) - YEAR(DateOfBirth) BETWEEN 30 AND 49 THEN 'Betweenthirtyandforty'
ELSE 'overfiftyyears'
END) AS AgeCategory,
assessmentdailyloaninfo.Outstandingbalance,
assessmentdailyloaninfo.SumPaidInToDate
FROM
assessmentcustomers
LEFT JOIN
assessmentsales ON assessmentcustomers.MartCustomerId = assessmentsales.MartCustomerId
LEFT JOIN
assessmentdailyloaninfo ON assessmentsales.MartLoanId = assessmentdailyloaninfo.MartLoanId
WHERE
ActivityDate = '2018-08-01'
GROUP BY AgeCategory;Results
| AgeCategory | Outstandingbalance | SumPaidInToDate |
|---|