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";
faq/support/lead_export_query.txt · Last modified: 2019/04/10 08:51 (external edit)
Back to top
CC Attribution-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0