A complex SQL query written for codescreen challenge

sitesData.sql

-- Selects season name, group id and client id
WITH RECURSIVE getSeason(SeasonName, GroupID, SeasonID, ClientID) AS (
  SELECT
    DISTINCT Seasons.SeasonName,
    SeasonClients.GroupID,
    SeasonClients.SeasonID,
    SeasonClients.ClientID
  FROM SeasonClients
  INNER JOIN Seasons ON SeasonClients.SeasonID = Seasons.SeasonID
  WHERE
    SeasonClients.SeasonID = 80
),

-- Get Clients per site in the season
-- meet the required condition that client must be active
getClients (
  SiteID,
  SeasonName,
  SeasonID,
  ClientID,
  GroupID
) AS (
  SELECT
    DISTINCT "Groups".SiteID,
    getSeason.SeasonName,
    getSeason.SeasonID,
    getSeason.ClientID,
    "Groups".GroupID
  FROM getSeason
  INNER JOIN "Groups" ON "Groups".GroupID = getSeason.GroupID
  WHERE
    "Groups".Active = 1
    AND Groups.GroupID IN(getSeason.GroupID)
),

-- Get the client count per site
getClientCountPerSite(SeasonName, SiteID, ClientCountPerSite) AS (
  SELECT
    getClients.SeasonName,
    getClients.SiteID,
    COUNT(*) AS ClientCountPerSite
  FROM getClients
  GROUP BY
    getClients.SiteID
),

-- get the GroupCount per site
getGroupCountPerSite(SeasonName, SiteID, GroupCountPerSite) AS (
  SELECT
    SeasonName,
    SiteID,
    COUNT(*) AS GroupCountPerSite
  FROM (
      SELECT
        DISTINCT getClients.SeasonName,
        getClients.GroupID,
        getClients.SiteID
      FROM getClients
    )
  GROUP BY
    SiteID
),

-- Get the total Land size per site
getTotalLandSizePerSite(SiteID, TotalLandSizePerSite) AS (
  SELECT
    SiteID,
    SUM(SeasonClientInputChoices.Acres) AS TotalLandSizePerSite
  FROM getClients
  INNER JOIN SeasonClientInputChoices ON SeasonClientInputChoices.ClientID = getClients.ClientID
    AND SeasonClientInputChoices.SeasonID = getClients.SeasonID
    AND SeasonClientInputChoices.Acres > 0
  GROUP BY
    SiteID
),

-- get the sites having the required client and group counts and the total land size
getSites (
  sectorID,
  SiteName,
  SeasonName,
  ClientCountPerSite,
  GroupCountPerSite,
  TotalLandSizePerSite
) AS (
  SELECT
    DISTINCT Sites.SectorID,
    Sites.SiteName,
    getClientCountPerSite.SeasonName,
    getClientCountPerSite.ClientCountPerSite,
    getGroupCountPerSite.GroupCountPerSite,
    getTotalLandSizePerSite.TotalLandSizePerSite
  FROM getClientCountPerSite
  JOIN getGroupCountPerSite ON getGroupCountPerSite.SiteID = Sites.SiteID
  JOIN getTotalLandSizePerSite ON getTotalLandSizePerSite.SiteID = Sites.SiteID
  INNER JOIN Sites ON Sites.SiteID = getClientCountPerSite.SiteID
  WHERE
    getClientCountPerSite.SiteID = Sites.SiteID
),

-- Get the sectors having the sites
getSectors(
  SiteName,
  SectorName,
  SeasonName,
  ClientCountPerSite,
  GroupCountPerSite,
  TotalLandSizePerSite,
  DistrictID
) AS (
  SELECT
    getSites.SiteName,
    Sectors.SectorName,
    getSites.SeasonName,
    getSites.ClientCountPerSite,
    getSites.GroupCountPerSite,
    getSites.TotalLandSizePerSite,
    Sectors.DistrictID
  FROM getSites
  INNER JOIN Sectors ON Sectors.SectorID = getSites.SectorID
  WHERE
    getSites.SectorID = Sectors.SectorID
  ORDER BY
    getSites.SiteName ASC
)

-- Select the district, joining the sector data to the return value
SELECT
  DistrictName,
  SeasonName,
  SectorName,
  SiteName,
  ClientCountPerSite,
  GroupCountPerSite,
  TotalLandSizePerSite
FROM getSectors
INNER JOIN Districts ON Districts.DistrictID = getSectors.DistrictID
WHERE
  getSectors.districtID = Districts.DistrictID
ORDER BY
  getSectors.SectorName ASC;