มาฝึกทำโปรเจค “Cafe Sale Analysis” ด้วย SQL แบบในชีวิตจริงกัน

เคยสงสัยกันไหมว่า Data Analyst ในชีวิตจริง พวกเราทำงานกันอย่างไร ?

สำหรับผู้ที่สนใจอยากลองมาทำงานสาย Data การฝึกฝนทำโปรเจคกับข้อมูลเสมือนจริงจะช่วยให้รับมือกับการสัมภาษณ์งาน และสามารถทำให้มั่นใจได้ว่าเมื่อเข้าไปทำงานจริงจะทำงานได้

ในบทความนี้ เราจะชวนมาวิเคราะห์ข้อมูลด้วยการเขียนโปรแกรม SQL ในธีม “Cafe Sale Analysis” สำหรับคนที่กำลังเริ่มเรียนรู้การใช้ SQL ก็สามารถทำไปพร้อม ๆ กันได้เลย

อยากฝึกโจทย์ Data แนวไหนอีก ? ทักมาบอกทีมงานเราได้ที่ Post นี้เลย – ทีม Datayolk

สิ่งที่จะได้เรียนรู้จากการลองทำโปรเจคนี้

1. คำสั่งพื้นฐานของ SQL ซึ่งประกอบไปด้วย Filter Data, Sorting Data, Aggregation
2. การเชื่อมตารางข้อมูล (Join table)
3. การสร้างตารางชั่วคราวด้วย WITH Clause
4. การเขียนคำสั่งย่อย (Subqueries)
5. การใช้คำสั่ง CASE Statement

ปัญหาทางธุรกิจ

สถานการณ์ของคาเฟ่ย่านใจกลางเมือง

ณ คาเฟ่เปิดใหม่ย่านใจกลางเมืองที่ลูกค้าส่วนใหญ่เป็นคนทำงานออฟฟิศ ตั้งแต่เปิดร้านมาก็ทำยอดขายได้ดี มีลูกค้าประจำที่ทำบัตรสมาชิกกับทางร้าน แต่ปัญหาที่ร้านกำลังเจออยู่คือสต๊อกสินค้าไม่พอดีกับความต้องการซื้อ บางทีก็สต็อกขายน้อยไปขายไม่ได้ บางทีก็สต็อกของเยอะไป ต้นทุนจมเยอะ

เราในฐานะ Data Analyst ต้องช่วยแนะนำให้ร้านสามารถสต็อกสินค้าได้อย่างเหมาะสม จากข้อมูลการใช้จ่ายของลูกค้ารายคน (Invoices Table), ข้อมูลสินค้าในร้าน (Items Table) และข้อมูลลูกค้าสมาชิก (Customers Table)

ทำโจทย์ Cafe Sale Analysis ด้วย Replit

ในบทความนี้เราใช้ Replit ที่ใช้งานสะดวกมาก ไม่ต้อง Install Program ก็สามารถใช้งานได้ เพียงเข้าไปที่ CafeSale Project แล้วกด Run ก็สามารถเริ่มเขียน SQL เพื่อวิเคราะห์ข้อมูลได้เลย แต่หากติดปัญหาอะไร อย่าลืมอ่านวิธีการใช้งานด้านข้างนะ

คำอธิบายชุดข้อมูลในโจทย์

ข้อมูลลูกค้า (Customers Table) จำนวน 10 คน ประกอบด้วย

ข้อมูลรายการสินค้า (Items Table) จำนวน 10 รายการ ประกอบด้วย

ข้อมูลการใช้จ่าย (Invoices Table) จำนวน 20 รายการ ประกอบด้วย

1. หายอดขายรวมของแต่ละสินค้าแต่ละรายการ เรียงตามลำดับไอดีของสินค้า

ให้เรียกดูข้อมูล item_id, item_name และ total_sales เรียงลำดับตาม item_id

วิเคราะห์โจทย์ข้อที่ 1

สำหรับโจทย์ข้อที่ 1 มีหลักการคิดดังนี้

1. เรียกดูข้อมูล item_id, item_name ทั้งสองเป็นค่าที่มีในตาราง Invoices อยู่แล้ว แต่ total_sales เป็นตัวแปรที่ถูกคำนวณจากค่า quantity และ price (total_sales = price * quantity) จากในตาราง Invoices และ Items ดังนั้นตามโจทย์ต้องใช้ JOIN Clause เพื่อเชื่อมข้อมูลสองตารางเพื่อตอบคำถามที่โจทย์กำหนดก่อน
2. เพื่อที่จะได้ผลรวมยอดขายเป็นตารางคล้าย Pivot Table จะต้องใช้ GROUP BY item_id, item_name เพื่อทำให้ผลรวมของแต่ละ item_id, item_name อยู่ในบรรทัดเดียวกัน
3. ใช้ GROUP BY ร่วมกับ ORDER BY เพื่อช่วยจัดลำดับเรียงตามไอดีของรายการสินค้าจาก item_id จากน้อยไปมาก

ทำข้อ 1 เสร็จแล้วใช่ไหม ดูเฉลยกัน

SELECT    
    i.item_id,
    i.item_name,
    SUM(i.price * inv.quantity) AS total_sales
FROM
    Items i
JOIN
    Invoices inv ON i.item_id = inv.item_id
GROUP BY
    i.item_id, i.item_name
ORDER BY
    i.item_id;

2. หายอดขายสะสมของลูกค้าแต่ละคน เรียงลำดับจากยอดขายสะสมมากไปน้อย

ให้เรียกดูข้อมูล customer_id, email และคำนวณ cumulative_sales จากนั้นลำดับ cumulative_sales จากมากไปน้อย

วิเคราะห์โจทย์ข้อที่ 2

มีหลักคิดเหมือนโจทย์ข้อแรก แต่เปลี่ยนวิธีการทำ GROUP BY

ทำข้อ 2 เสร็จแล้วใช่ไหม ดูเฉลยกัน

SELECT
    c.customer_id,
    c.email,
    SUM(i.price * inv.quantity) AS cumulative_sales
FROM
    Customers c
JOIN
    Invoices inv ON c.customer_id = inv.customer_id
JOIN
    Items i ON inv.item_id = i.item_id
GROUP BY
    c.customer_id, c.email
ORDER BY
    cumulative_sales DESC;

3. ให้จำแนกรายการสินค้า Dairy Products หรือ Non-Dairy Products

ให้เรียกข้อมูลทั้งหมดจากตาราง Items และสร้าง product_category โดยมีเงื่อนไขว่าถ้า item_id ประกอบด้วย 3, 4, 5, 8 และ 9 ให้บันทึกเป็น “Dairy Product” นอกเหนือจากนั้นให้บันทึกเป็น “Non-Dairy Product”

วิเคราะห์โจทย์ข้อที่ 3

สำหรับโจทย์ในข้อนี้เป็นการใช้คำสั่ง CASE statement เพื่อระบุเงื่อนไขสามารถเขียนในรูปแบบนี้

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS alias_name
FROM
    table_name;

จากโจทย์เขียนออกมาาเป็นเงื่อนไข ได้แบบนี้

• เมื่อ item_id อยู่ใน 3, 4, 5, 8, 9 ให้เป็น “Dairy Product”
• นอกจากนั้น ให้เป็น “Non-Dairy Product”

ทำข้อ 3 เสร็จแล้วใช่ไหม ดูเฉลยกัน

SELECT
    item_id,
    item_name,
    price,
    invoice_id,
CASE
    WHEN item_id IN (3, 4, 5, 8, 9) THEN 'Dairy Product'
ELSE 'Non-Dairy Product'
    END AS product_category
FROM
    Items;

4. คำนวณยอดขายสินค้าประเภท Dairy Products และ Non-Dairy Products พร้อมทั้งหาสัดส่วนยอดขายของสินค้าทั้งสอง

ให้เรียกข้อมูล product_category, total_quantity_sold และ percentage_sold ซึ่ง

• product_category ประกอบด้วย Dairy Product และ Non-Dairy Product
• total_quantity_sold คือผลรวมของ quantity
• percentage_sold คือสัดส่วนของ Dairy Product ต่อสินค้าทั้งหมดและส่วนของ Non-Dairy Product ต่อสินค้าทั้งหมด

วิเคราะห์โจทย์ข้อที่ 4

เราจะยกเงื่อนไข product_category ที่ได้ทำในข้อ 3 มาใช้ต่อด้วย WITH Clause เขียนได้แบบนี้

WITH ProductCategories AS (
    SELECT
        i.item_id,
        i.item_name,
        CASE
            WHEN i.item_id IN (3, 4, 5, 8, 9) THEN 'Dairy Product'
            ELSE 'Non-Dairy Product'
        END AS product_category
    FROM
        Items i
)

จากนั้นค่อยคำนวณ total_quantity_sold จากตาราง Invoices และคำนวณหา percentage_sold จากตาราง Invoices อย่าลืม Join ตาราง ProductCategories เข้ากับตาราง Invoices นะ

ทำข้อ 4 เสร็จแล้วใช่ไหม ดูเฉลยกัน

WITH ProductCategories AS (
    SELECT
        i.item_id,
        i.item_name,
        CASE
            WHEN i.item_id IN (3, 4, 5, 8, 9) THEN 'Dairy Product'
            ELSE 'Non-Dairy Product'
        END AS product_category
    FROM
        Items i
)
SELECT
    pc.product_category,
    SUM(inv.quantity) AS total_quantity_sold,
    (SUM(inv.quantity) * 100 / (SELECT SUM(quantity) FROM Invoices)) AS percentage_sold
FROM
    ProductCategories pc
JOIN
    Invoices inv ON pc.item_id = inv.item_id
GROUP BY
    pc.product_category;

5. คำนวณยอดขายรวมของแต่ละวันในสัปดาห์ (Sunday to Saturday)

ให้เรียกข้อมูล day_of_week และ total_sold ซึ่งสำหรับ SQLite จะใช้ฟังก์ชัน strftime() เพื่อ Extract order_date เป็น day_of_week

day_of_week 0 แทน วันอาทิตย์, 1 แทน วันจันทร์, …, 6 แทน วันเสาร์

วิเคราะห์โจทย์ข้อที่ 5

สำหรับโจทย์ข้อที่ 5 ให้เรียกดูข้อมูล day_of_week และ total_sold ดังนั้นตามโจทย์ต้อง Join สองตารางเพื่อตอบคำถามที่โจทย์กำหนด

ทำข้อ 5 เสร็จแล้วใช่ไหม ดูเฉลยกัน

SELECT
    strftime('%w', inv.order_date) AS day_of_week,
    SUM(i.price * inv.quantity) AS total_sold
FROM
    Invoices inv
JOIN
    Items i ON inv.item_id = i.item_id
GROUP BY
    day_of_week
ORDER BY
    day_of_week

6. คำนวณยอดขายรวมแต่ละวันในสัปดาห์จำแนกตามสินค้าประเภท Dairy และ Non-Dairy

ให้เรียกข้อมูล day_of_week, product_category และ total_sold ซึ่งก่อนหน้านี้เราได้สร้างเงื่อนไข product_category สามารถเรียกใช้ด้วย WITH Clause ได้เลย และใช้ข้อมูลการ Extract Date ในข้อที่ 5 มาประยุกต์ใช้กับข้อนี้ได้ด้วย ไปลุยกันเลย

วิเคราะห์โจทย์ข้อที่ 6

สำหรับโจทย์ข้อที่ 6 ให้เรียกดูข้อมูล day_of_week, product_category และ total_sold ดังนั้นตามโจทย์ต้อง Join ตาราง Invoices และตาราง Items เพื่อให้ได้ total_sold และ Join ตาราง Items กับ ProductCategories เพื่อให้ได้ product_category ตามแผนภาพ

ทำข้อ 6 เสร็จแล้วใช่ไหม ดูเฉลยกัน

WITH ProductCategories AS (
    SELECT
        i.item_id,
        i.item_name,
        CASE
            WHEN i.item_id IN (3, 4, 5, 8, 9) THEN 'Dairy Product'
            ELSE 'Non-Dairy Product'
        END AS product_category
    FROM
        Items i
)

SELECT
    strftime('%w', inv.order_date) AS day_of_week,
    pc.product_category,
    SUM(i.price * inv.quantity) AS total_sold
FROM
    Invoices inv
JOIN
    ProductCategories pc ON inv.item_id = pc.item_id
JOIN
    Items i ON inv.item_id = i.item_id
GROUP BY
    day_of_week, pc.product_category
ORDER BY
    day_of_week, pc.product_category;

ทำเสร็จทุกข้อแล้ว มาลองสรุป Insight ให้ Cafe นี้กัน (เติมลงในช่องว่าง)

• สินค้าประเภท …. มียอดขายสูงสุด 27.5$
• ลูกค้ารหัส 2 มียอดซื้อสินค้าสะสมมากที่สุดเท่ากับ … $
• สินค้าที่เป็น Dairy Product ประกอบด้วย …
• สัดส่วนของสินค้าประเภท Dairy Product ต่อสินค้าทั้งหมดเท่ากับ … %
• วันที่ขายดีที่สุดของ Cafe แห่งนี้คือวัน … ควรเตรียมสินค้าให้พร้อมขาย
• วัน … ขายสินค้าประเภท Dairy Product ได้น้อยที่สุด ควรลดสต๊อกสินค้าเพื่อสินค้าเหลือที่เสียง่าย

จบลงแล้วสำหรับโปรเจค Cafe Sale Analysis ที่ชวนมาทำโจทย์ SQL ซึ่งลักษณะคำถามแบบนี้ เหล่าคนทำงานข้อมูลก็ใช้คำสั่งพื้นฐานเหล่านี้ในชีวิตประจำวัน

ซึ่งรูปแบบการเขียนอาจจะทำต่างออกไปขึ้นอยู่กับการคำนึงเรื่องประสิทธิภาพ แต่สำหรับโจทย์ที่เราพึ่งทำกันไป ส่วนมากจะประยุกต์กับโจทย์อื่น ๆ เพื่อใช้ทดสอบในคนทำงานสายข้อมูลในระดับพื้นฐาน เดี๋ยวครั้งหน้าจะชวนไปทำโจทย์อื่น ๆ ที่น่าสนใจและใช้บ่อย ๆ ในคนทำงานสายข้อมูลกัน

บทความที่เกี่ยวข้อง

June 22, 2024June 22, 2024Data 25

มาฝึกใช้ Google Sheet Query Function ผ่าน Food Delivery Data กันเถอะ

มาเรียนรู้การใช้ Google Sheet Query Function ผ่านการฝึกวิเคราะห์ข้อมูลแพลตฟอร์ม Food Delivery กันเถอะ

April 7, 2024April 7, 2024Data 111

มาฝึกทำโปรเจค “Customer Review” ด้วย SQL แบบในชีวิตจริงกัน

เคยสงสัยกันไหมกันว่า Data Analyst ในชีวิตจริงพวกเราทำงานกันอย่างไร ? ในบทความนี้จะชวนมาวิเคราะห์ข้อมูลด้วยการเขียนโปรแกรม SQL ในธีม “Customer Review”

March 30, 2024March 30, 2024Data 89

สำรวจวงการซื้อขายงานวิจัยผ่านข้อมูลจากเว็บไซต์ Science Publisher

จากการวิเคราะห์ข้อมูลบนเว็บไซต์ Science Publisher ที่ให้บริการขายลิขสิทธิ์บทความวิจัยทั่วโลก ในหนึ่งปีจะมีบทความที่ขายในเว็บไซต์นี้อย่างต่ำก็ 600 บทความ มูลค่ารวมทั้งปีจะอยู่ราว ๆ 100 ล้านบาท

March 24, 2024March 30, 2024Data 61

มาฝึกทำโปรเจค “Car Dealer” ด้วย SQL แบบในชีวิตจริงกัน

เคยสงสัยกันไหมกันว่า Data Analyst ในชีวิตจริงพวกเราทำงานกันอย่างไร ? ในบทความนี้จะชวนมาวิเคราะห์ข้อมูลด้วยการเขียนโปรแกรม SQL ในธีม “Car Dealer”

• Facebook
• Twitter
• Line

Data Analytics Data Driven Decision Making exploratory data analysis Quest sql

Apirat A.

แอมป์ Data Specialist ที่ทำงานให้กับบริษัทเอกชนและ Datayolk.net โดยมีเป้าหมายให้คนใช้ข้อมูลในการขับเคลื่อนธุรกิจและสิ่งต่าง ๆ พร้อมกับตัดสินใจใช้ข้อมูลบนพื้นฐานของความน่าเชื่อถือผ่านบทความ, Quiz และแหล่งความรู้ด้านข้อมูลอีกมากมายที่เราได้เตรียมไว้ อ่านรายละเอียดเพิ่มเติม