Validate Advanced SQL Engine Functions | Teradata Vantage - Validating Advanced SQL Engine Analytic Functions

For information about Advanced SQL Engine analytic functions, see Teradata Vantage™ - Advanced SQL Engine Analytic Functions, B035-1206.

From a client application (such as BTEQ or Teradata Studio), log on to the Vantage system. For example:
```
.logon systemname.your_company.com/hal
```
where systemname is the primary database node (the name by which the Vantage system is known to the network, also called the TDPID).

Create input tables:

create multiset table attribution_1 (user_id integer, event varchar(20), time_stamp timestamp);
insert into attribution_1 values(1, 'impression', '2001-09-27 23:00:01');
insert into attribution_1 values(1, 'impression', '2001-09-27 23:00:05');
insert into attribution_1 values(1, 'email', '2001-09-27 23:00:15');
insert into attribution_1 values(2, 'impression', '2001-09-27 23:00:31');
insert into attribution_1 values(2, 'impression', '2001-09-27 23:00:51');

create multiset table attribution_2 (user_id integer, event varchar(20), time_stamp timestamp);
insert into attribution_2 values(1, 'impression', '2001-09-27 23:00:19');
insert into attribution_2 values(1, 'socialnetwork', '2001-09-27 23:00:20');
insert into attribution_2 values(1, 'direct', '2001-09-27 23:00:21');
insert into attribution_2 values(1, 'referral', '2001-09-27 23:00:22');
insert into attribution_2 values(1, 'paidsearch', '2001-09-27 23:00:23');
insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:29');
insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:31');
insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:33');
insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:36');
insert into attribution_2 values(2, 'impression', '2001-09-27 23:00:38');

create multiset table conversion_event_table (conversion_events varchar(20));
insert into conversion_event_table values('socialnetwork');
insert into conversion_event_table values('paidsearch');

create multiset table excluding_event_table (excluding_events varchar(20));
insert into excluding_event_table values('email');

create multiset table optional_event_table (optional_events varchar(20));
insert into optional_event_table values('organicsearch');
insert into optional_event_table values('direct');
insert into optional_event_table values('referral');

create multiset table model1_table  (id integer, model varchar(30));
insert into model1_table values('0', 'segment_rows');
insert into model1_table values('1', '3:0.5:exponential:0.5,second');
insert into model1_table values('2', '4:0.3:weighted:0.4,0.3,0.2,0.1');
insert into model1_table values('3', '3:0.2:first_click:NA');

create multiset table model2_table (id integer, model varchar(30));
insert into model2_table values('0', 'segment_seconds');
insert into model2_table values('1', '6:0.5:uniform:NA');
insert into model2_table values('2', '8:0.3:last_click:NA');
insert into model2_table values('3', '6:0.2:first_click:NA');

[Optional, for BTEQ users only] Set the page width for wider output:
```
.WIDTH 1024
```

Run the following Attribution query:

SELECT * FROM Attribution (
ON attribution_1 AS InputTable1
PARTITION BY user_id ORDER BY time_stamp
ON attribution_2 AS InputTable2
PARTITION BY user_id ORDER BY time_stamp
ON conversion_event_table AS ConversionEventTable DIMENSION
ON excluding_event_table AS ExcludedEventTable DIMENSION
ON optional_event_table AS OptionalEventTable DIMENSION
ON model1_table AS FirstModel DIMENSION
ON model2_table AS SecondModel DIMENSION
USING
EventColumn ('event')
TimeColumn ('time_stamp')
WindowSize ('rows:10&seconds:20')
) AS dt ORDER BY user_id, time_stamp;

The output is similar to the following:

user_id event         time_stamp                 attribution         time_to_conversion 
------- ------------- -------------------------- ------------------- ------------------ 
      1 impression    2001-09-27 23:00:01.000000 0.28571428571428575              -19.0
      1 impression    2001-09-27 23:00:05.000000                 0.0                  ?
      1 impression    2001-09-27 23:00:19.000000  0.7142857142857143               -1.0
      1 socialnetwork 2001-09-27 23:00:20.000000                   ?                  ?
      1 direct        2001-09-27 23:00:21.000000                 0.5               -2.0
      1 referral      2001-09-27 23:00:22.000000                 0.5               -1.0
      1 paidsearch    2001-09-27 23:00:23.000000                   ?                  ?

Validate Advanced SQL Engine Functions | Teradata Vantage - Validating Advanced SQL Engine Analytic Functions - Teradata Vantage

Configuring Teradata Vantage™ After Installation