Skip to content

Instantly share code, notes, and snippets.

@ekumachidi
Last active November 25, 2018 15:38
Show Gist options
  • Save ekumachidi/992d0fc075612e2ed513f96049ba0a24 to your computer and use it in GitHub Desktop.
Save ekumachidi/992d0fc075612e2ed513f96049ba0a24 to your computer and use it in GitHub Desktop.
Instruction For this test, MySQL 5+ dialect of SQL is preferred, however, you can write your SQL in any other dialect you prefer. Just indicate that in your solution. \n Foryour solution, please send a single test file, begin solution to different questions using a comment, and end your queries with a semicolon (;). See db-fiddle here: https://w…
-- SCHEMA DEFINITION
CREATE TABLE customer (
`id` int,
`name` varchar(50),
`date_of_birth` date,
`phone_number` varchar(13),
`date_added` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
INSERT INTO customer
(`id`, `name`, `date_of_birth`, `phone_number`)
VALUES
(1, 'Tom B. Erichsen', CURDATE(), '0801234567'),
(2, 'Matti Karttunen', CURDATE(), '0802134567'),
(3, 'Wilman Kala', CURDATE(), '08031234567'),
(4, 'Abdul Rahman', CURDATE(), '08012345678'),
(5, 'Lateefat Abdul-Rahman', CURDATE(), '08022345678');
CREATE TABLE card_info (
`id` int,
`masked_pan` varchar(50),
`customer_id` int,
`card_type` varchar(50),
`bank_name` varchar(50),
`date_added` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES customer(id)
);
INSERT INTO card_info
(`id`, `masked_pan`, `customer_id`, `card_type`, `bank_name`)
VALUES
(1,'4222 **** **** 2222', 1, 'VERVE', 'GTB'),
(2,'4222 **** **** 2221', 2, 'VERVE', 'UBA'),
(3,'4222 **** **** 2223', 3, 'VERVE', 'ECO'),
(4,'4222 **** **** 2224', 4, 'VISA', 'ECO'),
(5,'539983****1234', 5, 'MASTERCARD', 'GTB')
;
CREATE TABLE payment (
`id` int,
`amount` int,
`card_id` int,
`customer_id` int,
`successful` bool,
`payment_date` timestamp DEFAULT CURRENT_TIMESTAMP,
`payment_service` varchar(50),
`payment_status` bool,
`description` varchar(50),
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES customer(id),
FOREIGN KEY (card_id) REFERENCES card_info(id)
);
INSERT INTO payment
(`id`, `amount`, `card_id`, `customer_id`, `successful`, `payment_status`, `payment_service`, `description`,`payment_date` )
VALUES
(1, 100000, 1, 1, TRUE, TRUE, 'ISW', 'Dummy text','2018-10-25 09:47:35'),
(2, 5000, 2, 2, TRUE, TRUE, 'ISW', 'Dummy text', '2018-10-26 09:47:35'),
(3, 5000, 1, 1, TRUE, TRUE, 'PST', 'Dummy text', '2018-11-25 09:47:35'),
(4, 700000, 1, 1, TRUE, TRUE, 'FLV', 'Dummy text', '2018-9-25 09:47:35'),
(5, 700000, 3, 3, TRUE, TRUE, 'FLV', 'Dummy text', '2018-10-28 09:47:35'),
(6, 9000000, null, 1, TRUE, TRUE, 'GTB', 'Dummy text', '2018-10-28 09:47:35'),
(8, 9000000, null, 1, 0, 0, 'UBA', 'Dummy text', default),
(7, 9000000, null, 2, TRUE, TRUE, 'FLV', 'Dummy text', default),
(9, 9000000, null, 2, 0, 0, 'ECO', 'Dummy text', default),
(10, 5000, 4, 4, TRUE, TRUE, 'PST', 'Dummy text', '2018-11-25 09:47:35'),
(11, 2000000, null, 4, 0, 0, 'ISW', 'Dummy text', '2018-9-25 09:47:35'),
(12, 700000, null, 4, TRUE, TRUE, 'FLV', 'Dummy text', '2018-10-28 09:47:35'),
(13, 5000, 1, 1, 0, 0, 'PST', 'Dummy text', '2018-11-25 09:47:35'),
(14, 700000, 1, 1, 0, 0, 'FLV', 'Dummy text', '2018-9-25 09:47:35'),
(15, 700000, 3, 3, 0, 0, 'FLV', 'Dummy text', '2018-10-28 09:47:35'),
(16, 9000000, null, 1, 0, 0, 'GTB', 'Dummy text', '2018-10-28 09:47:35'),
(17, 9000000, 5, 5, TRUE, TRUE, 'FLV', 'Dummy text', '2018-8-25 09:47:35'),
(18, 9000000, null,5, 0, 0, 'UBA', 'Dummy text', '2018-9-25 09:47:35'),
(19, 9000000, null, 5, 0, 0, 'ECO', 'Dummy text', default)
;
-- QUESTIONS AND ANSWERS
-- QUESTION 1
-- Total volume and value successfully paid by customer, for the month of October 2018
SELECT customer.name, count(payment.id) AS 'Total Volume', sum(payment.amount)/100 AS 'Total Value' FROM payment
INNER JOIN customer ON customer.id= payment.customer_id
WHERE year(payment_date) = 2018 AND mONth(payment_date) = 10
GROUP BY customer.name;
-- QUESTION 2
-- Give a report of the successful payments (volume & value) that were paid by card and the ones that were not paid by card over the months of September to October 2018.
SELECT payment_type, count(payment_type) AS 'Total Volume', sum(amount)/100 AS 'Total Value' FROM
(
SELECT * ,
CASE
WHEN card_id IS NOT NULL THEN 'CARD PAYMENT'
ELSE "NOT CARD"
END
AS payment_type
FROM payment
WHERE successful = true AND year(payment_date) = 2018 AND mONth(payment_date) BETWEEN 8 AND 10
)temp
GROUP BY payment_type
;
-- QUESTION 3
-- A customer with phone number 08012345678 reached out to have made a payment of N20,000, however, we did not post this payment against their account. Write a query to extract ALL payments made by that customer, starting from the most recent payment, indicating which ones were successful and which failed.
SELECT customer.name, customer.phONe_number, amount, payment_date, card_id, CASE WHEN successful <> 0 then "successful" else "failed" end AS 'status' FROM payment
INNER JOIN customer ON customer.id = payment.customer_id
WHERE customer.phONe_number = '08012345678'
ORDER BY payment_date DESC;
-- QUESTION 4
-- Write a query to give a list of the top 5 bank that have the most failed card payments.
SELECT bank_name, count(bank_name) AS 'tran_count' FROM payment
INNER JOIN card_info ON card_info.id = payment.card_id
WHERE successful = false
GROUP BY bank_name
ORDER BY tran_count DESC
LIMIT 5;
-- QUESTION 5
-- Bank A, reached out to complain that a payment was made fraudulently with a card with masked pan 539983****1234 in August 2018, write a query to extract ALL payments made using such card (if any), including the name & phone number of the customers that made such payment.
SELECT name, phONe_number, bank_name, card_type, mASked_pan, payment_status, amount FROM payment
INNER JOIN card_info ON card_info.id = payment.card_id
INNER JOIN customer ON customer.id = payment.customer_id
WHERE card_info.mASked_pan = '539983****1234';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment