Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    SQL Test

    Uploaded by

    Pom Nawapat Sriboonrueng

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    SQL Test

    Uploaded by

    Pom Nawapat Sriboonrueng
    13 views3 pages

    Original Title

    SQL test

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    13 views3 pages

    SQL Test

    Uploaded by

    Pom Nawapat Sriboonrueng

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 3

    Data sample in Another Sheet

    Question: Please write a query script in SQL for each of the following tasks.

    1.) Sum of sales of 'Thailand' and 'Philipines' country in June 2017, considering only order with 'DELIVERED' status

    Result example: country status sum_total_selling_price


    Thailand DELIVERED 5000
    Philipines DELIVERED 6000

    2.) Top 10 best selling item based on number of unit solds (quantity) for partner_name XYZ

    Result example: partner_name item_id sum_quantity


    XYZ 1001 100
    XYZ 1015 84
    … … …
    (top10) XYZ 1091 5

    3.) Distinct count of customer who has 'REJECTED' order that has '1001' as item_id in February and March 2017

    Result example: item_id sales_channel distinct_count_of_customer


    1001 AAA 12
    1001 BBB 45

    4.) Number of order in each province and percentage comparing to total number of order

    Result example: province number of order percentage of total


    กรุงเทพฯ 2 15%
    …

    5.) Commission fee for each order from partner XYZ which calculated from total selling price

    Result example: partner_name order_id order_date Commission rate total_selling_price Commission fee
    XYZ A0001 1-Jan-17 2% 100 2
    XYZ A0002 11-Jan-17 2% 20 0.4
    XYZ A0002 11-Jan-17 2% 120 2.4
    XYZ A0002 11-Jan-17 2% 200 4
    XYZ A0005 15-Apr-17 5% 100 5

    6.) Number of orders sold to each customer on each sales channel

    Result example: sales_channelcustomer_id number_of_orders


    AAA cust001 30
    AAA cust002 20
    BBB cust001 15
    BBB cust002 40

    7.) List of orders that are delivered to the invalid location (postcode that cannot be identified its provice)

    Result example: order_id partner_id destination_postal code


    A0007 3 ppp

    8.) Top 1 best selling item based on number of unit solds (quantity) for every partner

    Result example: partner_nameitem_id sum_quantity


    XYZ 1001 100
    QWE 1002 84
    data sample

    1 3

    SALES_ORDER PARTNER
    order_id partner_id
    partner_id partner_name
    item_id country
    item_quantity
    total_selling_price
    sales_channel partner_id partner_name country
    order_date 1 XYZ Thailand
    customer_id 2 QWE Singapore
    3 DFG Philipines
    order_id partner_id item_id item_quantity total_selling_price sales_channel order_date customer_id

    A0001 1 1001 1 100 AAA 1-Jan-17 cust001 4


    A0002 1 1005 2 20 BBB 11-Jan-17 cust002
    A0002 1 1003 4 120 BBB 11-Jan-17 cust002 RATECARD
    A0002 1 1001 2 200 BBB 11-Jan-17 cust002 partner_id
    A0003 2 1002 1 80 AAA 3-Mar-17 cust003 partner_name
    A0003 2 1008 6 75 AAA 3-Mar-17 cust003 period start
    A0005 1 1001 1 100 AAA 15-Apr-17 cust005 period end
    A0006 2 1003 5 150 AAA 1-Jun-17 cust006 Commission rate
    A0007 3 1001 2 200 BBB 21-Jun-17 cust007 country
    A0007 3 1005 1 10 BBB 21-Jun-17 cust007
    A0007 3 1004 9 60 BBB 21-Jun-17 cust007 partner_id partner_name period start period end Commission rate country
    1 XYZ 20170101 20170131 2% Thailand
    1 XYZ 20170201 99991231 5% Thailand
    2 QWE 20170101 99991231 3% Singapore
    3 DFG 20170101 99991231 3% Philipines

    2 5
    ORDER_STATUS Area
    order_id postal_code
    partner_id province
    status
    delivered_date
    destination_postal code postalcode province
    10600 กรุงเทพฯ
    40560 ยะลา
    10500 กรุงเทพฯ
    55012 สงขลา
    order_id partner_id status delivered_datedestination_postal code 40620 น่าน
    A0001 1 REJECTED 1-Jan-17 10600 10800 กรุงเทพฯ
    A0002 1 DELIVERED 11-Jan-17 40560 59850 ประจวบ
    A0003 2 DELIVERED 3-Mar-17 10500
    A0005 1 DELIVERED 15-Apr-17 10800
    A0006 2 DELIVERED 1-Jun-17 40620
    A0007 3 IN_TRANSIT ppp

    You might also like