0

I have a customer data extension where customer records are saved with order numbers like this:

CustomerOrderDE:

CustomerID OrderID OrderTotal
1 101 $100
1 102 $200
2 103 $50
3 104 $75
3 105 $60

I am trying to create a query that should return unique CustomerID (if multiple rows found, return any one row - pick random one out of multiple)

This is how my destination data extension looks like:

DestinationDE (schema of the DE where output of the query will be saved as "Update" - Note: CustomerID is the primary key)

Field Names Data Type Primary Key
CustomerID text YES
OrderTotal text

Desired output:

CustomerID OrderTotal
1 $100 <-- any random
2 $50
3 $60 <!-- any random

Query 1 (with sub-query approach)

SELECT x.CustomerID,
x.OrderTotal
FROM 
(
SELECT a.*,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC) AS rank
FROM CustomerOrderDE a) x
where x.rank = 1

Query 2 (with inner join approach)

SELECT a.CustomerID,
a.OrderTotal
FROM CustomerOrderDE a
inner join
(
SELECT b.*,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC) AS rank
FROM CustomerOrderDE b) b
on a.OrderID = b.OrderID and a.CustomerID = b.CustomerID and rank = 1

Questions:

1) Is there any better / optimized approach that will perform well if working with a large data set?

2) Is there any issue or improvement that I can make in any of these existing queries?

1 Answer 1

1
  1. Since the relevant data is already available in the inner select, the join is unnecessary. Regarding performance, the camps are rather split between join vs sub-queries, until there is data to prove one is the faster on SFMC, I'll refrain from making any statements but i do personally tend to use sub-queries over joins unless i actually need a join.

  2. Neither of your queries would produce a true random results since they are being sorted by OrderID. Rerunning the query would essentially produce the same records. To achieve randomness, use ORDER BY newid() in your sub-query/join.

    SELECT x.CustomerID,
    x.OrderTotal
    FROM 
    (
    SELECT a.*,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY newid()) AS rank
    FROM CustomerOrderDE a) x
    where x.rank = 1
2
  • Hi Alex, thank you for sharing your thoughts. I just want to make sure I am following on #1 when you say "the join is unnecessary", which join are you referring to in #1? Looking at the query you shared with random newid(), it seems like it is using the same approach as #1 except randomness, correct?
    – kl2
    Sep 13, 2023 at 13:27
  • Unless your comment for join was for #2 approach?
    – kl2
    Sep 13, 2023 at 13:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .