Query, Sql Server - rextester

Language: Layout:

/*
Apa ini ?
    Ini cuma buat table Dummy biar lu lebih ngerti gimana hasil dari soal-soal yang dari sale stock.

Expected Output:
    member_id   Email                      phone_number      name                   address_city
    1           kuncoro.barot@btpn.com     081218543812      Kuncoro Wicaksono      Jabodetabek
    2           anisayunijenius@gmail.com  081290206641      Anisa Yuni Primasari   Bandung
*/

create table [member]
    (member_id int, email varchar(55), phone_number varchar(55), name varchar(55), address_city varchar(55));
    insert into [member] values
    (1, 'kuncoro.barot@btpn.com', '081218543812', 'Kuncoro Wicaksono', 'Jabodetabek'),
    (2, 'anisayunijenius@gmail.com', '081290206641', 'Anisa Yuni Primasari', 'Bandung');

create table [order]
(order_id int, member_id int, coupon_code varchar(55), total int, grand_total int, product_id int, created date);
insert into [order] values
(1, 1, 'Coupon Code #1', 3, 3, 1, '2017-01-24'),
(2, 1, 'Coupon Code #1', 2, 2, 1, '2017-11-24'),
(3, 1, 'Coupon Code #1', 1, 1, 1, '2017-11-24'),
(4, 1, 'Coupon Code #1', 1, 1, 1, '2017-03-24'),
(5, 2, 'Coupon Code #1', 4, 4, 1, '2017-11-24'),
(6, 2, 'Coupon Code #1', 3, 3, 1, '2017-11-24'),
(7, 2, 'Coupon Code #1', 4, 4, 1, '2017-11-24'),
(8, 2, 'Coupon Code #1', 2, 2, 1, '2017-11-24'),
(9, 2, 'Coupon Code #1', 4, 4, 1, '2017-11-24');

create table [shipping]
(shipping_id int, order_id int, address_city varchar(55));
insert into [shipping] values
(1, 1, 'Jabodetabek'),
(2, 2, 'Bandung'),
(3, 3, 'Jabodetabek'),
(4, 4, 'Sulawesi');

SELECT * FROM [member]
SELECT * FROM [order]
SELECT * FROM [shipping]

-- SOAL 1
    SELECT * FROM [shipping] WHERE ADDRESS_CITY = 'JABODETABEK'
-- SOAL 2
    SELECT A.*, B.jumlah_order 
    FROM [member] A LEFT JOIN 
        (SELECT member_id, COUNT(1) as jumlah_order FROM [order] GROUP BY member_id) B 
    ON A.member_id = b.member_id
-- SOAL 3
    SELECT address_city, sum(jumlah_order) as jumlah_order FROM (
        SELECT A.*, B.jumlah_order 
        FROM [member] A LEFT JOIN 
            (SELECT member_id, COUNT(1) as jumlah_order FROM [order] GROUP BY member_id) B 
        ON A.member_id = b.member_id
    ) A GROUP BY address_city
-- SOAL 4
    SELECT address_city, sum(jumlah_order) as jumlah_order FROM (
        SELECT A.*, B.jumlah_order 
        FROM [member] A LEFT JOIN 
            (SELECT member_id, COUNT(1) as jumlah_order FROM [order] GROUP BY member_id) B 
        ON A.member_id = b.member_id
    ) A GROUP BY address_city ORDER BY jumlah_order DESC
-- SOAL 5
    SELECT A.*, C.address_city as Shipping_address_city
    FROM [member] A LEFT JOIN [order] B ON A.member_id = B.member_id
        LEFT JOIN [shipping] C ON B.order_id = C.order_id
     WHERE A.address_city <> C.address_city
-- SOAL 6
    SELECT A.*, B.jumlah_belanja, B.total_order, (B.total_order/B.jumlah_belanja) as rata2_belanja
    FROM [member] A LEFT JOIN 
        (SELECT member_id, COUNT(1) as jumlah_belanja, SUM(total) as total_order FROM [order] GROUP BY member_id) B 
    ON A.member_id = b.member_id
    WHERE jumlah_belanja >= 2
-- SOAL 7
    SELECT *
    FROM [member] WHERE member_id IN
        ( SELECT member_id FROM [order] WHERE MONTH(CREATED) IN ( 1 , 3 )
        AND member_id NOT IN ( SELECT member_id FROM [order] WHERE MONTH(CREATED) = 2 )
        GROUP BY member_id )

View schema
Execution time: 0,09 sec, rows selected: 28, rows affected: 15, absolute service time: 0,25 sec