Data Analysis for E-Commerce Challenge

#to see the columns in the products table
DESCRIBE products;
#to see the rows and types of product categories
SELECT COUNT(product_id) AS jumlah_baris,
COUNT(DISTINCT category) AS jumlah_kategori
FROM products;
#to see a variable that is NULL
SELECT * FROM products
WHERE desc_product is NULL
OR category is NULL
OR base_price is NULL;
Figure 1. Output result for products data
#to see the columns in the orders table
DESCRIBE orders;
#to see the number of rows
SELECT COUNT(order_id) AS jumlah_baris FROM orders;
Figure 2. Output results for orders data
SELECT EXTRACT(YEAR_MONTH FROM created_at) AS date,
COUNT(order_id) AS total_transaction
FROM orders
GROUP BY date
ORDER BY date;
Figure 3. Output result for monthly transaction
#to see unpaid transactions
SELECT COUNT(order_id) AS total_transaction
FROM orders
WHERE paid_at IS NULL;
#to see paid transactions but not send
SELECT COUNT(order_id) AS total_transaction
FROM orders
WHERE paid_at IS NOT NULL AND delivery_at IS NULL;
#to see unsend transactions
SELECT COUNT(order_id) AS total_transaction
FROM orders
WHERE delivery_at IS NULL;
#to see transactions that send on the same day as the paid date
SELECT COUNT(order_id) AS total_transaction
FROM orders
WHERE paid_at IS NOT NULL
AND delivery_at IS NOT NULL
AND paid_at = delivery_at;
Figure 4. Output result for transaction data
#to see the total users
SELECT COUNT(DISTINCT user_id) AS user
FROM users;
#to see users who transact as buyers
SELECT COUNT(DISTINCT user_id) AS user_buyer
FROM users
JOIN orders
ON user_id = buyer_id;
#to see users who transact as sellers
SELECT COUNT(DISTINCT user_id) AS user_seller
FROM users
JOIN orders
ON user_id = seller_id;
#to see users who transact as buyers and sellers
SELECT COUNT(DISTINCT user_id) AS user_buyer_seller
FROM users
INNER JOIN (
SELECT buyer_id,
COUNT(1) AS user_transaksi_beli
FROM orders
GROUP BY 1
) AS buyer
ON buyer_id = user_id
INNER JOIN (
SELECT seller_id,
COUNT(1) AS user_transaksi_jual
FROM orders
GROUP BY 1
) AS seller
ON seller_id = user_id
ORDER BY 1;
Figure 5. Output result for user data
SELECT user_id, 
nama_user,
SUM(total) AS total
FROM users
INNER JOIN orders
ON user_id = buyer_id
GROUP BY user_id, nama_user
ORDER BY total DESC
LIMIT 5;
Figure 6. Output result for 5 users with the highest total amount of purchases
SELECT user_id, 
nama_user,
COUNT(order_id) AS total
FROM users
INNER JOIN orders
ON user_id = buyer_id
WHERE discount = 0
GROUP BY user_id, nama_user
ORDER BY total DESC
LIMIT 10;
Figure 7. Output result for users who have never used a discount
SELECT DISTINCT(SUBSTRING_INDEX(email, ‘@’, -1)) AS email
FROM users
INNER JOIN orders
ON user_id = seller_id
WHERE user_id = seller_id
ORDER BY email;
Figure 8. Output result for email sellers
SELECT a.desc_product AS name, 
SUM(b.quantity) AS quantity
FROM products AS a
INNER JOIN order_details AS b
ON a.product_id = b.product_id
INNER JOIN orders AS c
ON b.order_id = c.order_id
WHERE c.created_at BETWEEN ‘2019–12–01’ AND ‘2019–12–31’
GROUP BY name
ORDER BY quantity DESC
LIMIT 5;
Figure 9. Output result for the top 5 products in December 2019
SELECT seller_id, 
buyer_id,
total AS nilai_transaksi,
created_at AS tanggal_transaksi
FROM orders
WHERE buyer_id = 12476
ORDER BY 3 desc
LIMIT 10;
Figure 10. Output result for the 10 highest transactions of users 12476
SELECT EXTRACT(YEAR_MONTH FROM created_at) AS tahun_bulan, 
COUNT(1) AS jumlah_transaksi,
SUM(total) AS total_nilai_transaksi
FROM orders
WHERE created_at >= ‘2020–01–01’
GROUP BY 1
ORDER BY 1;
Figure 11. Output result for transactions per month in 2020
SELECT buyer_id, 
COUNT(1) AS jumlah_transaksi,
AVG(total) AS avg_nilai_transaksi
FROM orders
WHERE created_at >= ‘2020–01–01’ AND created_at<’2020–02–01'
GROUP BY 1
HAVING COUNT(1) >= 2
ORDER BY 3 DESC
LIMIT 10;
Figure 12. Output result for user with the highest average transaction in January 2020
SELECT nama_user AS nama_pembeli, 
total AS nilai_transaksi,
created_at AS tanggal_transaksi
FROM orders
INNER JOIN users
ON buyer_id = user_id
WHERE created_at >= ‘2019–12–01’
AND created_at <’2020–01–01'
AND total >= 20000000
ORDER BY 1;
Figure 13. Output result for big transactions in December 2019
SELECT category, 
SUM(quantity) AS total_quantity,
SUM(price) AS total_price
FROM orders
INNER JOIN order_details USING (order_id)
INNER JOIN products USING (product_id)
WHERE created_at >= ‘2020–01–01’
AND delivery_at IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
Figure 14. Output results for the best seller product category in 2020
SELECT nama_user AS nama_pembeli, 
COUNT(1) AS jumlah_transaksi,
SUM(total) AS total_nilai_transaksi,
MIN(total) AS min_nilai_transaksi
FROM orders
INNER JOIN users
ON buyer_id = user_id
GROUP BY user_id, nama_user
HAVING COUNT(1) > 5
AND MIN(total) > 2000000
ORDER BY 3 DESC;
Figure 15. Output result for high value
SELECT nama_user AS nama_pembeli,
COUNT(1) AS jumlah_transaksi,
COUNT(DISTINCT orders.kodepos) AS distinct_kodepos,
SUM(total) AS total_nilai_transaksi,
AVG(total) AS avg_nilai_transaksi
FROM orders
INNER JOIN users
ON buyer_id = user_id
GROUP BY user_id, nama_user
HAVING COUNT(1) >= 10
AND COUNT(1) = COUNT(DISTINCT orders.kodepos)
ORDER BY 2 DESC;
Figure 16. Output result for dropshipper
SELECT nama_user AS nama_pembeli,
COUNT(1) AS jumlah_transaksi,
SUM(total) AS total_nilai_transaksi,
AVG(total) AS avg_nilai_transaksi,
AVG(total_quantity) AS avg_quantity_per_transaksi
FROM orders
INNER JOIN users
ON buyer_id = user_id
INNER JOIN (
SELECT order_id,
SUM(quantity) AS total_quantity
FROM order_details
GROUP BY 1
) AS summary_order
USING (order_id)
WHERE orders.kodepos = users.kodepos
GROUP BY user_id, nama_user
HAVING COUNT(1) >= 8
AND AVG(total_quantity) > 10
ORDER BY 3 DESC;
Figure 17. Output results for offline reseller
SELECT nama_user AS nama_pengguna,
jumlah_transaksi_beli,
jumlah_transaksi_jual
FROM users
INNER JOIN (
SELECT buyer_id,
COUNT(1) AS jumlah_transaksi_beli
FROM orders
GROUP BY 1
) AS buyer
ON buyer_id = user_id
INNER JOIN (
SELECT seller_id,
COUNT(1) AS jumlah_transaksi_jual
FROM orders
GROUP BY 1
) AS seller
ON seller_id = user_id
WHERE jumlah_transaksi_beli >= 7
ORDER BY 1;
Figure 18. Output result for buyer and seller
SELECT EXTRACT(YEAR_MONTH FROM created_at) AS tahun_bulan,
COUNT(1) AS jumlah_transaksi,
AVG(DATEDIFF(paid_at, created_at)) AS avg_lama_dibayar,
MIN(DATEDIFF(paid_at, created_at)) min_lama_dibayar,
MAX(DATEDIFF(paid_at, created_at)) max_lama_dibayar
FROM orders
WHERE paid_at is NOT NULL
GROUP BY 1
ORDER BY 1;
Figure 19. Output result for transaction time

Be happy

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store