WITH prev_pool ("LeadId", "Id", "Name") AS (
  SELECT lm."LeadId", p."Id", p."Name"
  FROM "LeadMappings" AS lm
  JOIN "Pools" AS p ON lm."PoolId" = p."Id"
  ORDER BY lm."LastModifiedDate" DESC
  LIMIT 1
), live_accounts ("Id", "UserId", "Login") AS (
  SELECT "Id", "UserId", "Login"
  FROM "TradingAccounts"
  WHERE "TradingPlatformType" IN (2,4,6,8) -- LIVE Platform Types from ENUM
), demo_accounts ("Id", "UserId") AS (
  SELECT "Id", "UserId"
  FROM "TradingAccounts"
  WHERE "TradingPlatformType" IN (1,3,5,7) -- DEMO Platform Types from ENUM
), deposits ("Id", "UserId", "AmountUSD", "IsFirstDeposit", "CreatedDate") AS (
  SELECT t."Id", t."UserId", t."Amount" * t."ConversionRate" AS "AmountUSD", t."IsFirstDeposit", t."CreatedDate"
  FROM "Transactions" AS t
  JOIN "FundProcessors" AS f ON t."FundProcessorId" = f."Id"
  WHERE t."Amount" > 0
  AND t."StatusId" = 5 -- PspApproved
  AND t."IsFake" = false
  AND f."Id" <> 55     -- Bonus
  AND f."DepositType" NOTNULL
)
SELECT l."Id",
       l."CreatedDate",
       l."UserId",
       l."UserName",
       CONCAT(l."PhoneCountryCode", l."PhoneNumber") AS "Phone",
       CONCAT(l."FirstName", ' ', l."LastName") AS "FullName",
       CONCAT(cd."PhoneCountryCode", cd."PhoneNumber") AS "ContactDetailsPhone",
       CONCAT(pd."FirstName", ' ', pd."LastName") AS "PersonalDetailsFullName",
       l."CountryId" AS "CountryId",
       lc."Name" AS "CountryName",
       cd."CountryId" AS "ContactDetailsCountryId",
       cdc."Name" AS "ContactDetailsCountryName",
       wl."DomainName" AS "Domain",
       p."Id" AS "PoolId",
       p."Name" AS "PoolName",
       (SELECT "Id" FROM prev_pool AS pp WHERE pp."LeadId" = l."Id" AND pp."Id" <> l."PoolId") AS "PreviousPoolId",
       (SELECT "Name" FROM prev_pool AS pp WHERE pp."LeadId" = l."Id" AND pp."Id" <> l."PoolId") AS "PreviousPoolName",
       mi."Referrer",
       mi."OwnerReferrer",
       mi."CampaignId",
       c."Name" AS "CampaignName",
       mi."CompetitionId",
       mi."ADData",
       mi."AdsServer",
       mi."GoogleId",
       mi."Tag",
       mi."Tag1",
       mi."UtmCampaign",
       mi."UtmContent",
       mi."UtmCreative",
       mi."UtmMedium",
       mi."UtmSource",
       mi."UtmTerm",
       mi."CreativeId",
       l."CallbackStatus" AS "CallbackStatusId",
       u."SuppliedNecessaryDocuments",
       l."IsArchived",
       CASE WHEN (SELECT COUNT("Id") FROM live_accounts WHERE "UserId" = l."UserId") > 0 THEN 'Live'
            WHEN (SELECT COUNT("Id") FROM demo_accounts WHERE "UserId" = l."UserId") > 0 THEN 'Demo'
            ELSE 'Lead' END AS "RegistrationType",
       (SELECT COUNT("Id") FROM live_accounts WHERE "UserId" = l."UserId") AS "NumberLiveTradingAccounts",
       (SELECT COUNT("Id") FROM live_accounts WHERE "UserId" = l."UserId" AND "Login" NOTNULL) AS "NumberConvertedLiveTradingAccounts",
       u."LastOnlineDate",
       (SELECT GREATEST(
                   (SELECT MAX("CreatedDate")
                    FROM "LeadNotes"
                    WHERE "LeadId" = l."Id"),
                   (SELECT MAX("CreatedDate")
                    FROM "UserProfileNotes"
                    WHERE "UserId" = l."UserId")
               )) AS "LastNoteDate",
       (SELECT "CreatedDate" FROM "Transactions" WHERE "UserId" = l."UserId" AND "IsFirstDeposit" = true ORDER BY "CreatedDate" ASC LIMIT 1) AS "FirstDepositDate",
       (SELECT COUNT("Id") FROM "Transactions" WHERE "UserId" = l."UserId") > 0 AS "IsDepositor",
       (SELECT COALESCE(SUM("AmountUSD"), 0) FROM deposits WHERE "UserId" = l."UserId") AS "TotalDepositAmountUSD",
       l."SalesAgentId",
       sales."UserName" AS "SalesAgentName",
       l."RetentionAgentId",
       retention."UserName" AS "RetentionAgentName",
       l."ManagerAgentId",
       manager."UserName" AS "ManagerAgentName",
       (SELECT u."LastOnlineDate" >= (now() - (20 ||' minutes')::interval)) AS "IsWebOnline",
       (SELECT "Blocked" FROM "UserProfileBlockHistories" WHERE "UserId" = l."UserId" ORDER BY "CreatedDate" DESC LIMIT 1)
FROM "Leads" AS l
JOIN "LeadMarketingInfos" AS mi ON l."Id" = mi."LeadId"
JOIN "Campaigns" AS c ON mi."CampaignId" = c."Id"
JOIN "BusinessUnits" AS b on l."BusinessUnitId" = b."Id"
JOIN "Portals" AS wl ON b."PortalId" = wl."Id"
JOIN "Pools" AS p ON l."PoolId" = p."Id"
JOIN "UserProfile" u ON l."UserId" = u."UserId"
JOIN "UserProfileContactDetails" AS cd ON u."UserId" = cd."UserId"
JOIN "UserProfilePersonalDetails" AS pd ON u."UserId" = pd."UserId"
JOIN "Countries" AS lc ON l."CountryId" = lc."Id"
JOIN "Countries" AS cdc ON cd."CountryId" = cdc."Id"
JOIN "Users" AS sales ON l."SalesAgentId" = sales."UserId"
JOIN "Users" AS retention ON l."RetentionAgentId" = retention."UserId"
JOIN "Users" AS manager ON l."ManagerAgentId" = manager."UserId";