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…

OneFi SQL Test – QA Support Candidate.sql

-- 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';