Converting SQL queries and stored procedures into LookML coding

[harshpahuja]

Please help me understand converting some SQL query (see demo example below) into equivalent LookML coding; 
explaining whether dimension/measures/views to achieve same data table structure in Looker/LookML.

SELECT 
        order_date, status,
        SUM(variableA) result_LabelName,    --how calculations needs to be converted in LookML?                                        
        COUNT(*) variableB                                                
                                                        
    INTO #dummyTable1     --Dummy Temporary table for values (how to work with temporary tables in LookML?)
    FROM (
        SELECT  
            ISNULL(after.order_date,before.order_date) order_date,        --explanation: if 'after date' is null then 'before date' is stored in order_date otherwise 'after date' is stored    
                       -- how to work with NULL/ISNULL in LookML?

            CASE                                                         -- how to work with CASES equivalently in LookML?
                WHEN before.order_date IS NOT NULL AND after.order_date IS NOT NULL THEN 'GREEN'
                WHEN after.order_date IS NOT NULL THEN 'YELLOW'
                ELSE 'RED'
            END status
            
        FROM #dummyTable2
        INNER JOIN #MainTable after                            --how to work with joins in LookML?
            ON afterDate = after.order_date
        FULL OUTER JOIN #MainTable before
            ON beforeDate = before.order_date
            AND after.custid = before.custid
            
    ) referenceName
    GROUP BY order_date, status