Help in re-write a query

• Friday, February 01, 2013 9:05 AM

   

   
  

  0

  Hi All,

  I need help in re-writing the below query using CTE or any other method which would help in better performance.

  Here is sample test data and requirement.

  
  create table Users
  (
   [user_id] int identity(1,1) primary key,
   Email varchar(50),
   Gender char(1),
   Age int,
   name varchar(100)
   )
   go
   
   
  create table Subscriptions
  (
  subscription_id  int identity(1,1) primary key,
  [user_id] int,
  subscription_type varchar(50),
  active_indicator varchar(3)
  )
  ALTER TABLE Subscriptions ADD CONSTRAINT uc_user_id UNIQUE ([user_id],subscription_type)
  ALTER TABLE Subscriptions  ADD FOREIGN KEY ([user_id]) REFERENCES Users([user_id])
  go

  create table Transactions
  (
  subscription_id int not null,
  [action] varchar(50) not null,
  [timestamp] datetime not null
  )
  ALTER TABLE Transactions  ADD CONSTRAINT pk_Transactions PRIMARY KEY (subscription_id,[action],[timestamp])
  ALTER TABLE Transactions  ADD FOREIGN KEY (subscription_id) REFERENCES Subscriptions(subscription_id)
  go

  
  insert into Users(Email,Gender,Age,name)
  select 'a@a.com','m',30,'rob'
  union all
  select 'a@a.com','m',31,'robert'
  union all
  select 'b@b.com','f',18,'lucie'
  union all
  select 'b@b.com','f',22,'lulu'
  union all
  select 'c@c.com','m',10,'kim'
  union all
  select 'c@c.com','f',18,'kim'
  union all
  select 'c@c.com','f',08,'kim'
  union all
  select 'd@d.com','f',18,'JJ'
  union all
  select 'd@d.com','m',22,'Jay'
  union all
  select 'e@e.com','f',88,'Bill'
  union all
  select 'e@e.com','f',88,'Will'
  union all
  select 'e@e.com','f',60,'Will'
  union all
  select 'f@f.com','m',70,'Geor'

  
  insert into Subscriptions([user_id],subscription_type,active_indicator)
  select 2,'Magazine','Yes'
  union all
  select 3,'Music CD','No'
  union all
  select 3,'Magazine','Yes'
  union all
  select 3,'Video','Yes'
  union all
  select 8,'Magazine','Yes'
  union all
  select 9,'Video','Yes'
  union all
  select 10,'Magazine','No'
  union all
  select 13,'Magazine','Yes'

  insert into Transactions
  select 1,'Renewal','2002-sep-10'
  union all
  select 2,'Renewal','2002-Jan-01'
  union all
  select 2,'Cancellation','2002-Feb-01'
  union all
  select 3,'Renewal','2002-Aug-20'
  union all
  select 4,'Renewal','2002-Aug-01'
  union all
  select 4,'Renewal','2002-Sep-01'
  union all
  select 5,'Renewal','2002-Aug-01'
  union all
  select 6,'Renewal','2001-Sep-01'
  union all
  select 7,'Renewal','2002-Sep-01'
  union all
  select 7,'Cancellation','2002-Sep-10'

  Requirement

  =========

  Generate a list of unique email addresses with the latest name, gender and age for a user with that email

  The selection criteria limits the list to users which never subscribed to anything; or; users with inactive subscriptions; or;
  users with active subscriptions that renewed between Sep 1st and Sep 30th of any year

  answer should be:

  a@a.com         m         31        robert
  b@b.com         f           22        lulu
  c@c.com         f           08        kim
  d@d.com         m         22        Jay
  e@e.com         f           60        Will

  My Solution

  ========
  

  
  select Email, Gender,Age, name from Users a
  where user_id in
  (select MAX(user_id) from Users b
    where user_id in 
      (

      -- users never subscribed for anything
      select USER_ID from Users where user_id not in (select user_id from Subscriptions)
      union all
      -- users with inactive subscriptions;
      select [user_id] from Subscriptions where active_indicator = 'No'
      union all
      --users with active subscriptions that renewed between Sep 1st and Sep 30th of any year
      select [user_id] from  Subscriptions where subscription_id in (
      select subscription_id from Transactions where action = 'Renewal' and datepart(month,timestamp) = 9
      and subscription_id in (select subscription_id from Subscriptions where active_indicator='yes') )
   )
  and b.email = a.email
  )

  Thanks in advance.

  • Reply
  • Quote