DoomQL

WITH camera_space AS (          -- rotate world into camera space
    SELECT surface_id, depth_scaled, lateral_scaled,
           z_bottom, z_top, surface_kind, material,
           effective_light, sector_id
    FROM doomql_episode1
    WHERE map_name = 'E1M1'
      AND world_x BETWEEN cam.x - draw_dist AND cam.x + draw_dist
      AND world_y BETWEEN cam.y - draw_dist AND cam.y + draw_dist
), visible_space AS (            -- frustum + draw-distance clip
    SELECT * FROM camera_space
    WHERE depth_scaled BETWEEN near AND far
      AND lateral_scaled * focal
          BETWEEN -half_w * depth_scaled
              AND  half_w * depth_scaled
)
SELECT lateral_scaled, depth_scaled, z_bottom, z_top,
       surface_kind, material, sector_id,
       avg(effective_light) AS avg_light,
       count(*) AS samples, surface_id
FROM visible_space               -- reduce observations to visible surfaces
GROUP BY lateral_scaled, depth_scaled, z_bottom, z_top,
         surface_kind, material, sector_id, surface_id
ORDER BY depth_scaled, lateral_scaled, surface_kind, surface_id;