-- find warp_id that have had all of their skycell's differenced but have not
-- yet been magic'd
SELECT
    warp_id
FROM
    (SELECT
        warpRun.warp_id,
        warpSkyfile.fault as f1,
        diffSkyfile.fault as f2,
        warpSkyCellMap.skycell_id as wscm_s,
        warpSkyfile.skycell_id as wsf_s,
        diffInputSkyfile.skycell_id as s2
    FROM warpRun
    -- find all of the skycells that came out of a single exposure
    JOIN warpSkyCellMap
        USING(warp_id)
    LEFT JOIN warpSkyfile
        USING(warp_id, skycell_id, tess_id)
    -- find out which skycells have been diff'd
    LEFT JOIN diffInputSkyfile
        USING(warp_id, skycell_id, tess_id)
    LEFT JOIN diffRun
        USING(diff_id)
    LEFT JOIN diffSkyfile
        USING(diff_id)
    -- find out which diff'd skycells have been added to a magic run
    LEFT JOIN magicInputSkyfile
        USING(diff_id)
    WHERE
        warpRun.state = 'stop'
        AND (diffRun.state = 'stop' OR diffRun.state IS NULL)
    --    AND (warpSkyfile.fault = 0 || warpSkyfile.fault IS NULL)
        AND (diffInputSkyfile.template = 1 || diffInputSkyfile.template IS NULL)
        AND (diffInputSkyfile.kind = 'warped' || diffInputSkyfile.kind IS NULL)
    --    AND (diffSkyfile.fault = 0 || diffSkyfile.fault IS NULL)
        AND magicInputSkyfile.diff_id IS NULL
        AND warpSkyfile.warp_id IS NOT NULL
        AND diffInputSkyfile.warp_id IS NOT NULL
    GROUP BY
        warpRun.warp_id
    --    warpRun.warp_id, warpSkyCellMap.skycell_id, warpSkyCellMap.tess_id
    HAVING
        COUNT(warpSkyCellMap.skycell_id) = COUNT(warpSkyfile.skycell_id)
        AND COUNT(warpSkyCellMap.skycell_id) = COUNT(diffInputSkyfile.skycell_id)
        AND SUM(warpSkyfile.fault) = 0
        AND SUM(diffSkyfile.fault) = 0 
) as Foo
