3

Trying to take 3.8MM records and break them up into 3 groups.

Group 1 : 0 - 160,000 (already done) Group 2: 160,001 - 960,000 Group 3: 960,001 - 3,610,000

They need to be ordered by when they were actually created via the attribute [Created_At]

For the first group, it was pretty easy (and the last group should be rather easy too. But the middle is the challenge. Here's what I did for group A

SELECT TOP(160000) *
FROM ENT.[USERS]
ORDER BY Created_At DESC

I have a data guy on my end that proposed this (but SFMC doesn't like it - requires SELECT function to be first)

WITH OrderedUsers AS
(
    SELECT *, -- this won't work; you'll have to list out what you want
    ROW_NUMBER() OVER (ORDER BY Created_At) AS 'RowNumber'
    FROM ENT.[Users]
) 

SELECT * 
FROM OrderedUsers
WHERE RowNumber BETWEEN 160001 AND 960000
4

Adam Spriggs has done a very good blog about this on his site: T-SQL Split Queries

You would basically have to select your first populate then a left join with the old data extension and new one, where newDE.id IS NULL

He divides by percentages by the same logic should follow through, with you adding the ORDER BY condition.

For your use case:

Query 1 - Query1DE

SELECT TOP(160000)* 
FROM ENT.[USERS]
ORDER BY Created_At DESC

Query 2 Query2DE

SELECT TOP(8000000)* 
FROM ENT.[USERS] E
LEFT JOIN Query1DE Q1 ON
E.Id = Q1.ID
WHERE Q1.Id IS NULL
ORDER BY Created_At DESC

Query 3 Query3DE

SELECT TOP(26500000)* 
FROM ENT.[USERS] E
LEFT JOIN Query1DE Q1 ON
E.Id = Q1.ID
LEFT JOIN Query2DE Q2 ON
E.Id = Q2.ID
WHERE Q1.Id IS NULL
AND Q2.Id IS NULL
ORDER BY Created_At DESC
| improve this answer | |
  • 2
    Your "Data Guy" is correct, too, with a little platform specific modification (added below). Upvoting this, because both @Data_Kid and Adam are correct, also. It's a case of take your pick. – Macca Jun 15 '17 at 14:31
  • True it's all about perspective as your answer is also valid it gets my vote. I think this kind of behaviour points toward the "how do we encourage civility " post on meta – Samuel Dare Jun 15 '17 at 14:35
  • Damn... It won't let be do tit-for-tat upvoting! But yeah, there's a heap of stuff here that's neither right, nor wrong. Mostly, there's not enough information to make any affirmative decision. Appreciate it and agree. – Macca Jun 15 '17 at 15:07
  • I think the only adaptation I made to this is when using the '*'. I had to list out the column headers due to the nuance of SFMC. – Tom Callahan Jun 15 '17 at 18:13
3

Your Data Guy's correct, but Marketing Cloud's parser's very crude - SQL queries have to start with "SELECT"...

Try the first query like this:

SELECT  x.*
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY Created_At ) AS RowNumber, *
          FROM      ENT.[Users]
        ) x

And run this into your "OrderedUsers" table that's a duplicate of ENT.[Users] with an additional field, "RowNumber". Then, exactly as the Data Guy says, run this into your final result for your DE containing the segment:

SELECT * 
FROM OrderedUsers
WHERE RowNumber BETWEEN 160001 AND 960000
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.