Inclusive/Exclusive Customers per brand/category Teradata Script

Database
Enthusiast

Inclusive/Exclusive Customers per brand/category Teradata Script

Hi everyone,

 

I have 2 tables  :

-article info with information of article , brand, and category

-transactional data with info for articles bought per customer per day) :

create multiset volatile table article_info, no log( art_no    integer, art_desc   varchar(20)
,category_no integer,category_desc varchar(20)
,brand_no integer,brand_desc varchar(20)
)unique primary index (art_no)on commit preserve rows;

insert into article_info values (1,'COCA COLA COKE 1LT',000,'DRINKS', 123, 'COCA COLA'  );
insert into article_info values (2,'OWN BRAND COKE 1LT',000,'DRINKS', 001, 'OWN BRAND'  );
insert into article_info values (3,'VANILLA COKE 1LT',000,'DRINKS', 124, 'VANILLA'  );
insert into article_info values (4,'HEINEKEN 33CL',111,'BEER', 131, 'HEINEKEN'  );
insert into article_info values (5,'HARBIN 33CL',111,'BEER', 132, 'HARBIN'  );


create multiset volatile table transactional_table, no log(customer_id varchar (60)
,art_no    integer,date_id date format 'yyyy-mm-dd',sales int)unique primary index (customer_id,art_no)on commit preserve rows;

insert into transactional_table values ('A',1, '2019-01-01', 10  );
insert into transactional_table values ('B',1, '2019-03-02', 2  );
insert into transactional_table values ('B',2, '2019-03-02', 2  );
insert into transactional_table values ('C',3, '2019-02-02', 1  );
insert into transactional_table values ('D',1, '2019-03-01', 2  );
insert into transactional_table values ('D',3, '2019-03-01', 2  );

 

The desired output would be a table as following :

Category : All categories existing

Brand_1: all brands existing per category

Brand_2 : "OTHERS" , wich are all brands that exist in each category, excluding the one in "Brand_1" field

Capture.PNG

Taking as example the firts line output:

common_cust is # customers that bought coca cola and other brands from "DRINKS" category ( customer B and D)

exclusive_cust_brand_1 are customers that bought only coca cola in "DRINKS"  (customer A)

exclusive_cust_brand_2 are customers that bought only other brands rather than Coca Cola in "DRINKS" category  (customer C)

 

Can anyone help me in the query for getting this data?

 

Thank you in advanced


Accepted Solutions
Teradata Employee

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Hi ApprenticeVS,

 

This one is a bit complex, but still doable.

I've done it first by building a matrix of (category, brand, customer) then affecting them to a customer_type, using combinaison of exists / not exists on same / different brands.

 

Here's my solution :

with cte_union_all as
(
select m.*
     , 'ecl1' as cust_type
  from cte_matrix as m
 where exists (select null
                 from cte_join as j1
                where j1.category_desc = m.category_desc
                  and j1.customer_id   = m.customer_id
                  and j1.brand_desc    = m.brand_desc)
   and not exists (select null
                     from cte_join as j2
                    where j2.category_desc = m.category_desc
                      and j2.customer_id   = m.customer_id
                      and j2.brand_desc   <> m.brand_desc)
 union all
select m.*
     , 'ccc' as cust_type
  from cte_matrix as m
 where exists (select null
                 from cte_join as j1
                where j1.category_desc = m.category_desc
                  and j1.customer_id   = m.customer_id
                  and j1.brand_desc    = m.brand_desc)
   and exists (select null
                 from cte_join as j2
                where j2.category_desc = m.category_desc
                  and j2.customer_id   = m.customer_id
                  and j2.brand_desc   <> m.brand_desc)
 union all
select m.*
     , 'ecl2' as cust_type
  from cte_matrix as m
 where not exists (select null
                     from cte_join as j1
                    where j1.category_desc = m.category_desc
                      and j1.customer_id   = m.customer_id
                      and j1.brand_desc    = m.brand_desc)
   and exists (select null
                 from cte_join as j2
                where j2.category_desc = m.category_desc
                  and j2.customer_id   = m.customer_id
                  and j2.brand_desc   <> m.brand_desc)
)
  ,  cte_matrix as
(
    select art.category_desc
         , art.brand_desc
         , ttb.customer_id
      from article_info        as art
cross join transactional_table as ttb
  group by art.category_desc
         , art.brand_desc
         , ttb.customer_id
)
  ,  cte_join as
(
select art.*
     , ttb.customer_id
  from article_info        as art
  join transactional_table as ttb on ttb.art_no = art.art_no
)
  select category_desc
       , brand_desc
       , cust_type
       , count(customer_id) as nb_cust
    from cte_union_all
group by category_desc
       , brand_desc
       , cust_type;

Category  Brand      Cust_Type  Nb_Cust
--------  ---------  ---------  -------
DRINKS    COCA COLA  ccc              2
DRINKS    COCA COLA  ecl1             1
DRINKS    COCA COLA  ecl2             1
DRINKS    OWN BRAND  ccc              1
DRINKS    OWN BRAND  ecl2             3
DRINKS    VANILLA    ccc              1
DRINKS    VANILLA    ecl1             1
DRINKS    VANILLA    ecl2             2

You still have to do an outer join + the pivot but numbers seems good.

1 ACCEPTED SOLUTION
12 REPLIES 12
Teradata Employee

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Hi ApprenticeVS,

 

This one is a bit complex, but still doable.

I've done it first by building a matrix of (category, brand, customer) then affecting them to a customer_type, using combinaison of exists / not exists on same / different brands.

 

Here's my solution :

with cte_union_all as
(
select m.*
     , 'ecl1' as cust_type
  from cte_matrix as m
 where exists (select null
                 from cte_join as j1
                where j1.category_desc = m.category_desc
                  and j1.customer_id   = m.customer_id
                  and j1.brand_desc    = m.brand_desc)
   and not exists (select null
                     from cte_join as j2
                    where j2.category_desc = m.category_desc
                      and j2.customer_id   = m.customer_id
                      and j2.brand_desc   <> m.brand_desc)
 union all
select m.*
     , 'ccc' as cust_type
  from cte_matrix as m
 where exists (select null
                 from cte_join as j1
                where j1.category_desc = m.category_desc
                  and j1.customer_id   = m.customer_id
                  and j1.brand_desc    = m.brand_desc)
   and exists (select null
                 from cte_join as j2
                where j2.category_desc = m.category_desc
                  and j2.customer_id   = m.customer_id
                  and j2.brand_desc   <> m.brand_desc)
 union all
select m.*
     , 'ecl2' as cust_type
  from cte_matrix as m
 where not exists (select null
                     from cte_join as j1
                    where j1.category_desc = m.category_desc
                      and j1.customer_id   = m.customer_id
                      and j1.brand_desc    = m.brand_desc)
   and exists (select null
                 from cte_join as j2
                where j2.category_desc = m.category_desc
                  and j2.customer_id   = m.customer_id
                  and j2.brand_desc   <> m.brand_desc)
)
  ,  cte_matrix as
(
    select art.category_desc
         , art.brand_desc
         , ttb.customer_id
      from article_info        as art
cross join transactional_table as ttb
  group by art.category_desc
         , art.brand_desc
         , ttb.customer_id
)
  ,  cte_join as
(
select art.*
     , ttb.customer_id
  from article_info        as art
  join transactional_table as ttb on ttb.art_no = art.art_no
)
  select category_desc
       , brand_desc
       , cust_type
       , count(customer_id) as nb_cust
    from cte_union_all
group by category_desc
       , brand_desc
       , cust_type;

Category  Brand      Cust_Type  Nb_Cust
--------  ---------  ---------  -------
DRINKS    COCA COLA  ccc              2
DRINKS    COCA COLA  ecl1             1
DRINKS    COCA COLA  ecl2             1
DRINKS    OWN BRAND  ccc              1
DRINKS    OWN BRAND  ecl2             3
DRINKS    VANILLA    ccc              1
DRINKS    VANILLA    ecl1             1
DRINKS    VANILLA    ecl2             2

You still have to do an outer join + the pivot but numbers seems good.

Teradata Employee

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Wasn't satisfied with all the exists / not exists, so here is another version (which should run faster) :

with cte_group as
(
  select category_desc
       , brand_desc
       , cust_type
       , count(customer_id) as nb_cust
    from cte_cust_type
group by category_desc
       , brand_desc
       , cust_type
)
  ,  cte_cust_type as
(
    select m.category_desc
         , m.brand_desc
         , m.customer_id
         , case
             when m.brand_desc = max(j.brand_desc)
              and m.brand_desc = min(j.brand_desc)
             then 'ecl1'
             when (m.brand_desc = max(j.brand_desc)
               or  m.brand_desc = min(j.brand_desc))
              and count(distinct j.brand_desc) > 1
             then 'ccc'
             when m.brand_desc <> max(j.brand_desc)
              and m.brand_desc <> min(j.brand_desc)
             then 'ecl2'
             else 'none'
           end as cust_type
      from cte_matrix as m
 left join cte_join   as j  on j.category_desc = m.category_desc
                           and j.customer_id   = m.customer_id
  group by m.category_desc
         , m.brand_desc
         , m.customer_id
)
  ,  cte_matrix as
(
    select art.category_desc
         , art.brand_desc
         , ttb.customer_id
      from article_info        as art
cross join transactional_table as ttb
  group by art.category_desc
         , art.brand_desc
         , ttb.customer_id
)
  ,  cte_join as
(
select art.*
     , ttb.customer_id
  from article_info        as art
  join transactional_table as ttb on ttb.art_no = art.art_no
)
  select category_desc as Category
       , brand_desc    as Brand_1
       , 'OTHERS'      as Brand_2
       , sum(case cust_type when 'ccc'  then nb_cust else 0 end) as common_cust
       , sum(case cust_type when 'ecl1' then nb_cust else 0 end) as exclusive_cust_brand_1
       , sum(case cust_type when 'ecl2' then nb_cust else 0 end) as exclusive_cust_brand_2
    from cte_group
group by category_desc
       , brand_desc
order by 1 desc, 2 asc;


Category  Brand_1    Brand_2  common_cust  exclusive_cust_brand_1  exclusive_cust_brand_2
--------  ---------  -------  -----------  ----------------------  ----------------------
DRINKS    COCA COLA  OTHERS             2                       1                       1
DRINKS    OWN BRAND  OTHERS             1                       0                       3
DRINKS    VANILLA    OTHERS             1                       1                       2
BEER      HARBIN     OTHERS             0                       0                       0
BEER      HEINEKEN   OTHERS             0                       0                       0
Enthusiast

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Hello Waldar,

 

Thank you for the reply!

 

I have just one question: What if I want to choose only specific period in transactional data, and specific categories in article info table & transactional data ?

 

Thank you!

Teradata Employee

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

You have to add those filters inside the CTE-s cte_join and cte_matrix ; it shouldn't be an issue.

Enthusiast

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Hi Waldar,

 

Thank you so much for your help it was really helpfull!

 

Just one more thing which i am not being able to adapt: I have in your query the customers, what if I want to know the total amount of sales of each type of customers (ccc, ec11, ec12) instead of number of customers?

 

Thank you!

Enthusiast

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Actually I think I might get the answer ! If I just change in script customer Id for sum(sales) I think I can achieve the result . I will try later tomorrow :)

Thank you for all the support!
Teradata Employee

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Exactly, note you don't have to replace the customer count, you can add sales informations.

I've also impleted some filters here :

with cte_group as
(
  select category_desc
       , brand_desc
       , cust_type
       , count(customer_id) as nb_cust
       , sum(sales)         as sales
    from cte_cust_type
group by category_desc
       , brand_desc
       , cust_type
)
  ,  cte_cust_type as
(
    select m.category_desc
         , m.brand_desc
         , m.customer_id
         , coalesce(sum(j.sales), 0) as sales
         , case
             when m.brand_desc = max(j.brand_desc)
              and m.brand_desc = min(j.brand_desc)
             then 'ecl1'
             when (m.brand_desc = max(j.brand_desc)
               or  m.brand_desc = min(j.brand_desc))
              and count(distinct j.brand_desc) > 1
             then 'ccc'
             when m.brand_desc <> max(j.brand_desc)
              and m.brand_desc <> min(j.brand_desc)
             then 'ecl2'
             else 'none'
           end as cust_type
      from cte_matrix as m
 left join cte_join   as j  on j.category_desc = m.category_desc
                           and j.customer_id   = m.customer_id
  group by m.category_desc
         , m.brand_desc
         , m.customer_id
)
  ,  cte_matrix as
(
    select art.category_desc
         , art.brand_desc
         , ttb.customer_id
      from article_info        as art
cross join transactional_table as ttb
     where art.category_desc = 'DRINKS'
       and ttb.date_id      >= date '2019-02-01'
  group by art.category_desc
         , art.brand_desc
         , ttb.customer_id
)
  ,  cte_join as
(
select art.*
     , ttb.customer_id
     , ttb.sales
  from article_info        as art
  join transactional_table as ttb on ttb.art_no = art.art_no
 where art.category_desc = 'DRINKS'
   and ttb.date_id      >= date '2019-02-01'
)
  select category_desc as Category
       , brand_desc    as Brand_1
       , 'OTHERS'      as Brand_2
       , sum(case cust_type when 'ccc'  then nb_cust else 0 end) as cmc
       , sum(case cust_type when 'ecl1' then nb_cust else 0 end) as ec1
       , sum(case cust_type when 'ecl2' then nb_cust else 0 end) as ec2
       , sum(case cust_type when 'ccc'  then sales   else 0 end) as cms
       , sum(case cust_type when 'ecl1' then sales   else 0 end) as es1
       , sum(case cust_type when 'ecl2' then sales   else 0 end) as es2
    from cte_group
group by category_desc
       , brand_desc
order by 1 desc, 2 asc;

Category  Brand_1    Brand_2  cmc  ec1  ec2  cms  es1  es2
--------  ---------  -------  ---  ---  ---  ---  ---  ---
DRINKS    COCA COLA  OTHERS     2    0    1    8    0    1
DRINKS    OWN BRAND  OTHERS     1    0    2    4    0    5
DRINKS    VANILLA    OTHERS     1    1    1    4    1    4

I've shortened the final column names for forum readability.

Highlighted
Enthusiast

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Hi Waldar,

 

Thank you !!!

 

I just found one problem when adapting to my data:

 

Because you use  max and min in this part of the script:

    select m.desc_categ         , m.desc_marca         , m.id_cliente         , case
             when m.desc_marca = max(j.desc_marca)
              and m.desc_marca = min(j.desc_marca)
             then 'ecl1'
             when (m.desc_marca = max(j.desc_marca)
               or  m.desc_marca = min(j.desc_marca))
              and count(distinct j.desc_marca) > 1
             then 'ccc'
             when m.desc_marca <> max(j.desc_marca)
              and m.desc_marca <> min(j.desc_marca)
             then 'ecl2'
             else 'none'
           end as cust_type
      from cte_matrix as m
 left join cte_join   as j  on j.desc_categ = m.desc_categ
                           and j.id_cliente   = m.id_cliente
  group by m.desc_categ         , m.desc_marca         , m.id_cliente

 It does not take into account when a customer buys more than 2 brands.

 

If i change my initial data and include one more brand bought by customer B (article 5 from Brand Harbin):

create multiset volatile table article_info, no log( art_no    integer, art_desc   varchar(20)
,category_no integer,category_desc varchar(20)
,brand_no integer,brand_desc varchar(20)
)unique primary index (art_no)on commit preserve rows;

insert into article_info values (1,'COCA COLA COKE 1LT',000,'DRINKS', 123, 'COCA COLA'  );
insert into article_info values (2,'OWN BRAND COKE 1LT',000,'DRINKS', 001, 'OWN BRAND'  );
insert into article_info values (3,'VANILLA COKE 1LT',000,'DRINKS', 124, 'VANILLA'  );
insert into article_info values (4,'HEINEKEN 33CL',111,'BEER', 131, 'HEINEKEN'  );
insert into article_info values (5,'HARBIN 33CL',111,'BEER', 132, 'HARBIN'  );


create multiset volatile table transactional_table, no log(customer_id varchar (60)
,art_no    integer,date_id date format 'yyyy-mm-dd',sales int)unique primary index (customer_id,art_no)on commit preserve rows;

insert into transactional_table values ('A',1, '2019-01-01', 10  );
insert into transactional_table values ('B',1, '2019-03-02', 2  );
insert into transactional_table values ('B',2, '2019-03-02', 2  );
insert into transactional_table values ('B',5, '2019-03-02', 2  );
insert into transactional_table values ('C',3, '2019-02-02', 1  );
insert into transactional_table values ('D',1, '2019-03-01', 2  );
insert into transactional_table values ('D',3, '2019-03-01', 2  );

 In the counts of Harbin brand he will be counted as "exclusive buyer" of that brand while actually he is a common (ccc) one. He bought Harbin, Coca Cola and Own Brand.

 

Teradata Employee

Re: Inclusive/Exclusive Customers per brand/category Teradata Script

Hi ApprenticeVS,

 

I thought the types were category splitted, meaning one customer could be an exclusive buyer in BEER and also in DRINKS.

If not it changes things a bit. Can you confirm this to me ?