Databases
StackOverflow2013
Postsdbo
PONext action using analytic functions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONext action using analytic functions
    primarykey
    data
    text
    <p>For the need of a cohort analysis, I'm trying to get the next action of each customer (unsubscription, upgrade, downgrade...)</p> <p>I have a monhtly snapshot with the following data : </p> <pre><code>customer | month | last_action | last_action_date 1 01-01-2012 subscription 01-01-2012 1 02-01-2012 subscription 01-01-2012 1 03-01-2012 subscription 01-01-2012 1 04-01-2012 downgrade 04-01-2012 1 05-01-2012 downgrade 04-01-2012 1 06-01-2012 downgrade 04-01-2012 1 07-01-2012 unsubscription 07-01-2012 </code></pre> <p>As you can see, the action is known only at the month it is done, at the month 01-01-2012 we don't know yet the customer downgraded in 04-01-2012 so we can't analyse his usage behavior relative to his downgrade month. Same thing for unsubscription.</p> <p>The needed dataset is the following :</p> <pre><code>customer | month | downgrade_date | unsubscription_date 1 01-01-2012 04-01-2012 07-01-2012 1 02-01-2012 04-01-2012 07-01-2012 1 03-01-2012 04-01-2012 07-01-2012 1 04-01-2012 12-31-9999 07-01-2012 1 05-01-2012 12-31-9999 07-01-2012 1 06-01-2012 12-31-9999 07-01-2012 1 07-01-2012 12-31-9999 07-01-2012 </code></pre> <p>I could have easily the unsubscription date with the last_value analytic function but didn't find a way to get the downgrade date.</p> <p>Here is my SQL Query :</p> <pre><code>SELECT month_id, customer_id, CASE WHEN LAST_VALUE(last_action) OVER (PARTITION BY customer_id ORDER BY month_id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) = 'unsubscription' THEN LAST_VALUE(last_action_date) OVER (PARTITION BY customer_id ORDER BY month_id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ELSE TO_DATE('99991231', 'yyyymmdd') END unsubscription_date FROM my_table ; </code></pre> <p>Any way to get the "next" action date like in the "downgrade_date".</p> <p>I'm using Oracle.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMaz
    1. USuser2018454
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONext action using analytic functions
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PONext action using analytic functions
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCheck out the `lead()` function.
      singulars
      1. PONext action using analytic functions
      1. USa_horse_with_no_name
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload