SQL Insert into Sessions only where the Actions has multiple conditions RedshiftSQL












0















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:




  1. User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)

  2. 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.

  3. Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)

  4. 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










share|improve this question























  • 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
















0















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:




  1. User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)

  2. 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.

  3. Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)

  4. 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










share|improve this question























  • 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














0












0








0








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:




  1. User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)

  2. 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.

  3. Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)

  4. 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










share|improve this question














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:




  1. User 232 can do many stuff before in the same session (even for days if they never stop doing actions every hour)

  2. 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.

  3. Sessions table can have several time the same user achieving a purchase if the sessions has been restarted (> 1h in timeframe)

  4. 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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 25 '18 at 14:58









Ignacio BustosIgnacio Bustos

4892720




4892720













  • 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





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












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
});


}
});














draft saved

draft discarded


















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
















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'