Using CASE and WHENΒΆ

At times we might have to select values from multiple columns conditionally.

  • We can use CASE and WHEN for that.

  • Let us implement this conditional logic to come up with derived order_status.

    • If order_status is COMPLETE or CLOSED, set COMPLETED

    • If order_status have PENDING in it, then we will say PENDING

    • If order_status have PROCESSING or PAYMENT_REVIEW in it, then we will say PENDING

    • We will set all others as OTHER

  • We can also have ELSE as part of CASE and WHEN.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%sql DROP TABLE IF EXISTS sales

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

CREATE TABLE IF NOT EXISTS sales(
    sales_person_id INT,
    sales_amount FLOAT,
    commission_pct INT
)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

INSERT INTO sales VALUES
    (1, 1000, 10),
    (2, 1500, 8),
    (3, 500, NULL),
    (4, 800, 5),
    (5, 250, NULL)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

[]

%%sql

SELECT * FROM sales

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
sales_person_id sales_amount commission_pct
1 1000.0 10
2 1500.0 8
3 500.0 None
4 800.0 5
5 250.0 None 
%%sql

SELECT s.*,
    CASE WHEN commission_pct IS NOT NULL 
        THEN round((sales_amount * commission_pct / 100)::numeric, 2)
    ELSE 0
    END AS commission_amount
FROM sales s

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
sales_person_id sales_amount commission_pct commission_amount
1 1000.0 10 100.00
2 1500.0 8 120.00
3 500.0 None 0
4 800.0 5 40.00
5 250.0 None 0 
%%sql

SELECT DISTINCT order_status FROM orders
ORDER BY order_status

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
9 rows affected.
order_status
CANCELED
CLOSED
COMPLETE
ON_HOLD
PAYMENT_REVIEW
PENDING
PENDING_PAYMENT
PROCESSING
SUSPECTED_FRAUD 
%%sql

SELECT o.*,
    CASE WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status updated_order_status
1021 2013-07-30 00:00:00 10118 COMPLETE COMPLETED
4068 2013-08-17 00:00:00 12293 PENDING None
5881 2013-08-30 00:00:00 3715 CLOSED COMPLETED
7564 2013-09-09 00:00:00 8648 CLOSED COMPLETED
8766 2013-09-18 00:00:00 855 COMPLETE COMPLETED
8926 2013-09-19 00:00:00 10517 ON_HOLD None
9290 2013-09-21 00:00:00 11879 COMPLETE COMPLETED
9793 2013-09-24 00:00:00 9809 COMPLETE COMPLETED
9816 2013-09-24 00:00:00 1753 COMPLETE COMPLETED
14047 2013-10-20 00:00:00 6473 CLOSED COMPLETED 
%%sql

SELECT o.*,
    CASE WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
    ELSE order_status
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status updated_order_status
1021 2013-07-30 00:00:00 10118 COMPLETE COMPLETED
4068 2013-08-17 00:00:00 12293 PENDING PENDING
5881 2013-08-30 00:00:00 3715 CLOSED COMPLETED
7564 2013-09-09 00:00:00 8648 CLOSED COMPLETED
8766 2013-09-18 00:00:00 855 COMPLETE COMPLETED
8926 2013-09-19 00:00:00 10517 ON_HOLD ON_HOLD
9290 2013-09-21 00:00:00 11879 COMPLETE COMPLETED
9793 2013-09-24 00:00:00 9809 COMPLETE COMPLETED
9816 2013-09-24 00:00:00 1753 COMPLETE COMPLETED
14047 2013-10-20 00:00:00 6473 CLOSED COMPLETED 
%%sql

SELECT o.*,
    CASE 
        WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
        WHEN order_status ~ 'PENDING' THEN 'PENDING'
        ELSE 'OTHER'
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status updated_order_status
1021 2013-07-30 00:00:00 10118 COMPLETE COMPLETED
4068 2013-08-17 00:00:00 12293 PENDING PENDING
5881 2013-08-30 00:00:00 3715 CLOSED COMPLETED
7564 2013-09-09 00:00:00 8648 CLOSED COMPLETED
8766 2013-09-18 00:00:00 855 COMPLETE COMPLETED
8926 2013-09-19 00:00:00 10517 ON_HOLD OTHER
9290 2013-09-21 00:00:00 11879 COMPLETE COMPLETED
9793 2013-09-24 00:00:00 9809 COMPLETE COMPLETED
9816 2013-09-24 00:00:00 1753 COMPLETE COMPLETED
14047 2013-10-20 00:00:00 6473 CLOSED COMPLETED 
%%sql

SELECT o.*,
    CASE 
        WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
        WHEN order_status LIKE '%PENDING%' OR order_status IN ('PROCESSING', 'PAYMENT_REVIEW')
            THEN 'PENDING'
        ELSE 'OTHER'
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_customer_id order_status updated_order_status
1021 2013-07-30 00:00:00 10118 COMPLETE COMPLETED
4068 2013-08-17 00:00:00 12293 PENDING PENDING
5881 2013-08-30 00:00:00 3715 CLOSED COMPLETED
7564 2013-09-09 00:00:00 8648 CLOSED COMPLETED
8766 2013-09-18 00:00:00 855 COMPLETE COMPLETED
8926 2013-09-19 00:00:00 10517 ON_HOLD OTHER
9290 2013-09-21 00:00:00 11879 COMPLETE COMPLETED
9793 2013-09-24 00:00:00 9809 COMPLETE COMPLETED
9816 2013-09-24 00:00:00 1753 COMPLETE COMPLETED
14047 2013-10-20 00:00:00 6473 CLOSED COMPLETED 
%%sql

SELECT DISTINCT order_status,
    CASE 
        WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
        WHEN order_status LIKE '%PENDING%' OR order_status IN ('PROCESSING', 'PAYMENT_REVIEW')
            THEN 'PENDING'
        ELSE 'OTHER'
    END AS updated_order_status
FROM orders
ORDER BY updated_order_status
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
9 rows affected.
order_status updated_order_status
CLOSED COMPLETED
COMPLETE COMPLETED
SUSPECTED_FRAUD OTHER
CANCELED OTHER
ON_HOLD OTHER
PAYMENT_REVIEW PENDING
PENDING_PAYMENT PENDING
PROCESSING PENDING
PENDING PENDING
Handling NULL Values Exercises - Pre-Defined Functions