MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
     -- MySQL dump 10.13  Distrib 8.0.17, for Win64 (x86_64)
     --
     -- Host: localhost    Database: marketplace
     -- ------------------------------------------------------
     -- Server version	8.0.17
     
     /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
     /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
     /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
     /*!50503 SET NAMES utf8 */;
     /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
     /*!40103 SET TIME_ZONE='+00:00' */;
     /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
     /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
     /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
     /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
     
     --
     -- Table structure for table `banks`
     --
     
     DROP TABLE IF EXISTS `banks`;
     /*!40101 SET @saved_cs_client     = @@character_set_client */;
     /*!50503 SET character_set_client = utf8mb4 */;
     CREATE TABLE `banks` (
       `bank_id` int(11) NOT NULL,
       `bank_name` tinytext,
       PRIMARY KEY (`bank_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
     /*!40101 SET character_set_client = @saved_cs_client */;
     
     --
     -- Dumping data for table `banks`
     --
     
     LOCK TABLES `banks` WRITE;
     /*!40000 ALTER TABLE `banks` DISABLE KEYS */;
     INSERT INTO `banks` VALUES (1,'HSBC'),(2,'Ocean Bank'),(3,'BCA'),(4,'BRI'),(5,'BNI'),(6,'BTPN'),(7,'Bank Keadilan');
     /*!40000 ALTER TABLE `banks` ENABLE KEYS */;
     UNLOCK TABLES;
     
     --
     -- Table structure for table `customers`
     --
     
     DROP TABLE IF EXISTS `customers`;
     /*!40101 SET @saved_cs_client     = @@character_set_client */;
     /*!50503 SET character_set_client = utf8mb4 */;
     CREATE TABLE `customers` (
       `costumer_id` int(11) NOT NULL,
       `customer_name` text,
       `customer_age` int(11) DEFAULT NULL,
       `estimated_risk_level` text,
       `source` text,
       `created_date` datetime DEFAULT NULL,
       PRIMARY KEY (`costumer_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
     /*!40101 SET character_set_client = @saved_cs_client */;
     
     --
     -- Dumping data for table `customers`
     --
     
     LOCK TABLES `customers` WRITE;
     /*!40000 ALTER TABLE `customers` DISABLE KEYS */;
     INSERT INTO `customers` VALUES (1,'najib',45,'high','facebook','2019-08-13
 09:27:52'),(2,'ridho',19,'low','google','2019-08-13
 09:27:52'),(3,'ujang',25,'high','facebook','2019-08-13
 09:27:52'),(4,'nadya',23,'medium','instagram','2019-08-13
 09:27:52'),(5,'uli',22,'medium','google','2019-08-13
 09:27:52'),(6,'syahputra',35,'low','google','2019-08-13 09:27:52');
     /*!40000 ALTER TABLE `customers` ENABLE KEYS */;
     UNLOCK TABLES;
     
     --
     -- Table structure for table `leads`
     --
     
     DROP TABLE IF EXISTS `leads`;
     /*!40101 SET @saved_cs_client     = @@character_set_client */;
     /*!50503 SET character_set_client = utf8mb4 */;
     CREATE TABLE `leads` (
       `customer_id` int(11) NOT NULL,
       `product_id` int(11) DEFAULT NULL,
       `apply_date` datetime DEFAULT NULL,
       PRIMARY KEY (`customer_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
     /*!40101 SET character_set_client = @saved_cs_client */;
     
     --
     -- Dumping data for table `leads`
     --
     
     LOCK TABLES `leads` WRITE;
     /*!40000 ALTER TABLE `leads` DISABLE KEYS */;
     INSERT INTO `leads` 
     VALUES 
     (1,2,'2019-08-14 09:27:52'),
     (2,1,'2019-07-14 09:27:52'),
     (4,6,'2019-07-14 09:27:52'),
     (5,5,'2019-07-14 09:27:52'),
     (6,4,'2019-07-14 09:27:52');
     /*!40000 ALTER TABLE `leads` ENABLE KEYS */;
     UNLOCK TABLES;
     
     --
     -- Table structure for table `products`
     --
     
     DROP TABLE IF EXISTS `products`;
     /*!40101 SET @saved_cs_client     = @@character_set_client */;
     /*!50503 SET character_set_client = utf8mb4 */;
     CREATE TABLE `products` (
       `product_id` int(11) NOT NULL,
       `loan_amount` int(11) DEFAULT NULL,
       `interest_rate` float DEFAULT NULL,
       `accepted_risk_level` text,
       `bank_id` int(11) DEFAULT NULL,
       `created_date` datetime DEFAULT NULL,
       PRIMARY KEY (`product_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
     /*!40101 SET character_set_client = @saved_cs_client */;
     
     --
     -- Dumping data for table `products`
     --
     
     LOCK TABLES `products` WRITE;
     /*!40000 ALTER TABLE `products` DISABLE KEYS */;
     INSERT INTO `products` VALUES (1,1500,2.5,'low',1,'2019-08-13 09:27:52'),(2,2000,2.5,'high',1,'2019-08-13
 09:27:52'),(3,1200,5,'high',1,'2019-08-13
 09:27:52'),(4,2500,2.5,'medium',2,'2019-08-13
 09:27:52'),(5,1550,5,'medium',2,'2019-08-13 09:27:52');
     /*!40000 ALTER TABLE `products` ENABLE KEYS */;
     UNLOCK TABLES;
     /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
     
     /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
     /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
     /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
     /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
     /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
     /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
     /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
     
     -- Dump completed on 2019-08-13 22:31:35

Records: 7  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 5



Records: 5  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 3


SELECT * FROM `banks`;
SELECT * FROM `customers`;
SELECT * FROM `leads`;
SELECT * FROM `products`;


bank_id
bank_name


1
HSBC

2
Ocean Bank

3
BCA

4
BRI

5
BNI

6
BTPN

7
Bank Keadilan




costumer_id
customer_name
customer_age
estimated_risk_level
source
created_date


1
najib
45
high
facebook
2019-08-13 09:27:52

2
ridho
19
low
google
2019-08-13 09:27:52

3
ujang
25
high
facebook
2019-08-13 09:27:52

4
nadya
23
medium
instagram
2019-08-13 09:27:52

5
uli
22
medium
google
2019-08-13 09:27:52

6
syahputra
35
low
google
2019-08-13 09:27:52




customer_id
product_id
apply_date


1
2
2019-08-14 09:27:52

2
1
2019-07-14 09:27:52

4
6
2019-07-14 09:27:52

5
5
2019-07-14 09:27:52

6
4
2019-07-14 09:27:52




product_id
loan_amount
interest_rate
accepted_risk_level
bank_id
created_date


1
1500
2.5
low
1
2019-08-13 09:27:52

2
2000
2.5
high
1
2019-08-13 09:27:52

3
1200
5
high
1
2019-08-13 09:27:52

4
2500
2.5
medium
2
2019-08-13 09:27:52

5
1550
5
medium
2
2019-08-13 09:27:52


SELECT COUNT(customer_id) as monthly_count
FROM leads
WHERE year(apply_date) = 2019
GROUP BY MONTH(apply_date);

SELECT AVG(monthly_count) as loans_avg
FROM (
    SELECT COUNT(customer_id) as monthly_count
    FROM leads
    WHERE year(apply_date) = 2019
    GROUP BY MONTH(apply_date)
) t


monthly_count


1

4




loans_avg


2.5000


WITH monthly_avg as  (
    SELECT AVG(monthly_count) as loans_avg
    FROM (
        SELECT COUNT(customer_id) as monthly_count
        FROM leads
        WHERE year(apply_date) = 2019
        GROUP BY MONTH(apply_date)
        ) t
)
SELECT monthname(apply_date), COUNT(customer_id) as monthly_count
FROM leads
WHERE year(apply_date) = 2019
GROUP BY monthname(apply_date)
HAVING monthly_count > (SELECT 1.2 * loans_avg
                        FROM monthly_avg)



monthname(apply_date)
monthly_count


July
4
costumer_id	customer_name	customer_age	estimated_risk_level	source	created_date
1	najib	45	high	facebook	2019-08-13 09:27:52
2	ridho	19	low	google	2019-08-13 09:27:52
3	ujang	25	high	facebook	2019-08-13 09:27:52
4	nadya	23	medium	instagram	2019-08-13 09:27:52
5	uli	22	medium	google	2019-08-13 09:27:52
6	syahputra	35	low	google	2019-08-13 09:27:52
customer_id	product_id	apply_date
1	2	2019-08-14 09:27:52
2	1	2019-07-14 09:27:52
4	6	2019-07-14 09:27:52
5	5	2019-07-14 09:27:52
6	4	2019-07-14 09:27:52
product_id	loan_amount	interest_rate	accepted_risk_level	bank_id	created_date
1	1500	2.5	low	1	2019-08-13 09:27:52
2	2000	2.5	high	1	2019-08-13 09:27:52
3	1200	5	high	1	2019-08-13 09:27:52
4	2500	2.5	medium	2	2019-08-13 09:27:52
5	1550	5	medium	2	2019-08-13 09:27:52