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";
Back to top