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", (SELECT "Name" FROM "Countries" WHERE "Id" = l."CountryId") AS "CountryName", cd."CountryId" AS "ContactDetailsCountryId", (SELECT "Name" FROM "Countries" WHERE "Id" = cd."CountryId") 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 l."UserId" NOTNULL AND "UserId" = l."UserId") > 0 AS "IsDepositor", (SELECT COALESCE(SUM("AmountUSD"), 0) FROM deposits WHERE "UserId" = l."UserId") AS "TotalDepositAmountUSD", l."SalesAgentId", (SELECT "UserName" FROM "Users" WHERE "UserId" = l."SalesAgentId") AS "SalesAgentName", l."RetentionAgentId", (SELECT "UserName" FROM "Users" WHERE "UserId" = l."RetentionAgentId") AS "RetentionAgentName", l."ManagerAgentId", (SELECT "UserName" FROM "Users" WHERE "UserId" = l."ManagerAgentId") 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" LEFT 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" LEFT JOIN "UserProfile" u ON l."UserId" = u."UserId" LEFT JOIN "UserProfileContactDetails" AS cd ON u."UserId" = cd."UserId" LEFT JOIN "UserProfilePersonalDetails" AS pd ON u."UserId" = pd."UserId";