SQL Insert into Sessions only where the Actions has multiple conditions RedshiftSQL
By using Redshift SQL.
Given a table of Actions like this:
user.id | action | time
232 | view_website | 2018/10/25 13:00:00
365 | view_product | 2018/10/25 13:20:00 //This is another user (could be infinite users)
232 | view_product | 2018/10/25 13:22:00
...
232 | purchase_pro | 2018/10/25 14:10:00
...
I want to create a Sessions Table and insert only sessions of user.id that has reached to "purchase_pro" page, by tracking the start time and finish of the session knowing that session only work for 1h.
This means:
- User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)
- User 232 can do multiple purchases in the same session, I only want one record in the sessions table as long as they are in the same timeframe.
- Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)
- Sessions has to track only those who purchase and the entire timeframe of the session since they started.
In this manner Session should look like for user 232:
user.id | Start | Finish
232 | 2018/10/25 13:00:00 | 2018/10/25 15:10:00 // Notice 1h extra since last action in session.
I was approaching this by creating sessions for everybody and then deleting those who never reach to a purchase but I think this is not a proper approach in only one string of SQL.
Thanks in advance
sql session amazon-redshift insert-update
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
add a comment |
By using Redshift SQL.
Given a table of Actions like this:
user.id | action | time
232 | view_website | 2018/10/25 13:00:00
365 | view_product | 2018/10/25 13:20:00 //This is another user (could be infinite users)
232 | view_product | 2018/10/25 13:22:00
...
232 | purchase_pro | 2018/10/25 14:10:00
...
I want to create a Sessions Table and insert only sessions of user.id that has reached to "purchase_pro" page, by tracking the start time and finish of the session knowing that session only work for 1h.
This means:
- User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)
- User 232 can do multiple purchases in the same session, I only want one record in the sessions table as long as they are in the same timeframe.
- Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)
- Sessions has to track only those who purchase and the entire timeframe of the session since they started.
In this manner Session should look like for user 232:
user.id | Start | Finish
232 | 2018/10/25 13:00:00 | 2018/10/25 15:10:00 // Notice 1h extra since last action in session.
I was approaching this by creating sessions for everybody and then deleting those who never reach to a purchase but I think this is not a proper approach in only one string of SQL.
Thanks in advance
sql session amazon-redshift insert-update
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
Rather than asking for someone to do this for you, you should show what you have so far and ask specific questions to get it improved / corrected. please add your existing sql and working examples of input, current output and expected output data.
– Jon Scott
Nov 26 '18 at 8:17
add a comment |
By using Redshift SQL.
Given a table of Actions like this:
user.id | action | time
232 | view_website | 2018/10/25 13:00:00
365 | view_product | 2018/10/25 13:20:00 //This is another user (could be infinite users)
232 | view_product | 2018/10/25 13:22:00
...
232 | purchase_pro | 2018/10/25 14:10:00
...
I want to create a Sessions Table and insert only sessions of user.id that has reached to "purchase_pro" page, by tracking the start time and finish of the session knowing that session only work for 1h.
This means:
- User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)
- User 232 can do multiple purchases in the same session, I only want one record in the sessions table as long as they are in the same timeframe.
- Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)
- Sessions has to track only those who purchase and the entire timeframe of the session since they started.
In this manner Session should look like for user 232:
user.id | Start | Finish
232 | 2018/10/25 13:00:00 | 2018/10/25 15:10:00 // Notice 1h extra since last action in session.
I was approaching this by creating sessions for everybody and then deleting those who never reach to a purchase but I think this is not a proper approach in only one string of SQL.
Thanks in advance
sql session amazon-redshift insert-update
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
By using Redshift SQL.
Given a table of Actions like this:
user.id | action | time
232 | view_website | 2018/10/25 13:00:00
365 | view_product | 2018/10/25 13:20:00 //This is another user (could be infinite users)
232 | view_product | 2018/10/25 13:22:00
...
232 | purchase_pro | 2018/10/25 14:10:00
...
I want to create a Sessions Table and insert only sessions of user.id that has reached to "purchase_pro" page, by tracking the start time and finish of the session knowing that session only work for 1h.
This means:
- User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)
- User 232 can do multiple purchases in the same session, I only want one record in the sessions table as long as they are in the same timeframe.
- Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)
- Sessions has to track only those who purchase and the entire timeframe of the session since they started.
In this manner Session should look like for user 232:
user.id | Start | Finish
232 | 2018/10/25 13:00:00 | 2018/10/25 15:10:00 // Notice 1h extra since last action in session.
I was approaching this by creating sessions for everybody and then deleting those who never reach to a purchase but I think this is not a proper approach in only one string of SQL.
Thanks in advance
sql session amazon-redshift insert-update
sql session amazon-redshift insert-update
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
asked Nov 25 '18 at 14:58
Ignacio BustosIgnacio Bustos
4792719
4792719
Rather than asking for someone to do this for you, you should show what you have so far and ask specific questions to get it improved / corrected. please add your existing sql and working examples of input, current output and expected output data.
– Jon Scott
Nov 26 '18 at 8:17
add a comment |
Rather than asking for someone to do this for you, you should show what you have so far and ask specific questions to get it improved / corrected. please add your existing sql and working examples of input, current output and expected output data.
– Jon Scott
Nov 26 '18 at 8:17
Rather than asking for someone to do this for you, you should show what you have so far and ask specific questions to get it improved / corrected. please add your existing sql and working examples of input, current output and expected output data.
– Jon Scott
Nov 26 '18 at 8:17
Rather than asking for someone to do this for you, you should show what you have so far and ask specific questions to get it improved / corrected. please add your existing sql and working examples of input, current output and expected output data.
– Jon Scott
Nov 26 '18 at 8:17
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53468753%2fsql-insert-into-sessions-only-where-the-actions-has-multiple-conditions-redshift%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53468753%2fsql-insert-into-sessions-only-where-the-actions-has-multiple-conditions-redshift%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Rather than asking for someone to do this for you, you should show what you have so far and ask specific questions to get it improved / corrected. please add your existing sql and working examples of input, current output and expected output data.
– Jon Scott
Nov 26 '18 at 8:17