# Connection: local # Host: localhost # Saved: 2005-03-19 23:34:27 # use test1 drop table if exists account; create table account_merchant( account_id varchar(100) not null, merchant_id int unsigned not null, merchant_customer_category_id int unsigned not null, point_balance int unsigned , dollar_balance float(10,2) , email_opt_in bit default 1, direct_mail_opt_in bit default 1, mailing_list_opt_in bit default 1, first_visit_date datetime not null , last_visit_date datetime, customer_category_spending float(10,2), customer_category_visits int unsigned, index (merchant_id), index (merchant_customer_category_Id), primary key (account_id,merchant_id) ) type=INNODB; drop table if exists transaction; create table transaction ( transaction_id bigint unsigned auto_increment not null primary key, transaction_quantity int not null, transaction_date datetime, merchant_id int unsigned not null, mobile_device_id varchar(100) not null, account_id varchar(100) not null, transaction_type_id int unsigned not null, transaction_amount float(10,2) not null ) type=INNODB; delete from account_merchant; insert into account_merchant (account_id,merchant_id,merchant_customer_category_id) values('0001',1,1), ('0002',1,2), ('0003',1,2); delete from transaction; insert into transaction(transaction_id,transaction_quantity,transaction_date,merchant_id,mobile_device_id,account_id,transaction_type_id,transaction_amount) values(1,1,'2004-12-01',1,1,'0001',1,23.23) ,(2,1,'2004-12-01',1,1,'0002',1,23.23) ,(3,1,'2004-12-01',1,1,'0003',1,23.23) ,(4,1,'2004-12-01',1,1,'0001',1,23.23) ,(5,1,'2004-12-01',1,1,'0002',1,23.23) ,(6,1,'2004-12-01',1,1,'0003',1,23.23) ,(7,1,'2004-12-01',1,1,'0003',1,23.23); set @rpt_date='2004-12-01'; set @merchant_id=1; # insert regular values select @rpt_date as week_of_date ,@merchant_id as merchant_id,t.account_id, count(distinct transaction_id) as spending_per_visit from account_merchant am left join (select * from transaction where transaction_date between @rpt_date and date_add(@rpt_date, interval 7 DAY) and merchant_id=@merchant_id ) as t on (t.account_id=am.account_id) group by t.account_id;