Project Summary

Improving the Understanding of SQL function to help access, read, manipulate, and analyze the data stored in the database and generate useful insights to drive an informed decision-making process, and this 2 main things that I did in this project :

1. Created SQL query based using aggregate functions,data manipulation, date format, joins, CTE,
2. Created the insights for the project

Project Background & Dataset

Project Background

Provide data and analysis regarding the condition of the company in the last month, including sales growth, customer growth,transaction growth, actual active customer, Explore and get the hands on Structured Query Language (SQL). As a data analyst, we are expected to use SQL to formating and cleaning the data, joining the data, using CTE and Window function also generating useful insights from the data.

1.DQLab Mart products

Based on the output, there are 6 products that have prices between 50,000 and 150,000.

2.Thumb drive at DQLab Mart

Based on the output, there are 2 products whose names contain the word flashdisk.

3.Title customers

Based on the output, there are 3 names of customers who have the title of S.H., or Ir., or Drs.

4.Sort Customer Names

Based on the output, customer names have been sorted from letter A to letter U

5.Sort Customer Names Without Titles

Show customer names and sort the results according to the nama_pelanggan from smallest to largest (A to Z), however titles should not be part of the order. Example: Ir. Agus Nugraha should be above Heidi Goh. Required column name: nama_pelanggan. The query will be performed using SQL:

Based on the output, customer names have been sorted from letter A to letter U regardless of the title.

6.The Longest Customer Name

Show customer name who has the longest name. If there is more than one person with the same name length, show all of them. Required column name: nama_pelanggan. The query will be performed using SQL:

Based on the output, there is one name which is the longest customer name that has 19 characters.

7.Longest and Shortest Customer’s Name with Title

Show customer names who has the longest name (on the top row) and the shortest name (on the next row). Title becomes part of the name. If there is more than one shortest or longest name, all of them must be showed. Required column name: nama_pelanggan. The query will be performed using SQL:

Based on the output, there is one name which is the longest customer name that has 19 characters and one name which is the shortest customer name that has 9 characters.

8.The quantity of products sold

Show the most sold products in terms of quantity. If there is more than one product with the same value, show all the products. Required column name: kode_produk, nama_produk,total_qty. The query will be performed using SQL:

Based on the output, there are 2 products that are sold the most.

9.Customers with the Highest Purchase Value

Who are the customers who spend the most money on shopping? If there are more than one customer with the same value, show all the customers. Required column name: kode_pelanggan, nama_pelanggan, total_harga The query will be performed using SQL:

Based on the output results, there is one customer who makes the most transactions.

10.Customers Who Have Never Shopped

Show a list of customers who have never made a transaction. Required column name: kode_pelanggan, nama_pelanggan, alamat. The query will be performed using SQL:

Based on the output results, there are 5 customers who have never made a transaction.

11.Shopping Transactions with a Shopping List more than 1

Show transactions that have more than one type of product. In other words, show transactions that have more than one row of data in tr_penjualan_detail table. Required column name: kode_transaksi, kode_pelanggan, nama_pelanggan, tanggal_transaksi, jumlah_detail The query will be performed using SQL:

Based on the output, there are 5 transactions that have the number of product sold for more than one type of product.