cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Analytics, ADQL and a join

Ashley.Ings
New Poster

Is it possible to use a join as part of the SQL to pull back some info collect by AppD? 

 

The problem is I have 3 parameters in AppD, username, connection-status and eventtime. Each user could be in a state of ‘connected’ or ‘disconnected’ so I want to pull back the information and then max the eventtime so it pulls back the most up to date connection status. I would normally do this in SQL with a join. I’ve tried to add a table reference to the ‘transaction’ table as t1 and then add the t1 to each of the fields even before I add the join, but I don’t think it support join / subqueries

 

Examples code

 

SELECT segments.userData.userName,
segments.userData.brand,
segments.userData.status
FROM transactions tt1
INNER JOIN (SELECT segments.userData.userName, MAX(eventdate) AS MaxDateTime FROM transaction GROUP BY segments.userData.userName) tt2 ON tt1.segments.userData.userName = tt2.segments.userData.userName AND tt1.eventdate = tt2.MaxDateTime
WHERE tt1.application = "aaa"
AND tt2.transactionName = "tttt"
AND tt2.segments.userData.status = "Connected"

 
0 REPLIES 0