mysql - Get result in single query rather then three different query -

table structure , sample data

create table if not exists `orders` (   `id` int(11) not null auto_increment,   `customer_id` int(11) not null,   `restaurant_id` int(11) not null,   `bill_id` int(11) not null,   `source_id` int(1) not null,   `order_medium_id` int(11) not null,   `purchase_method` varchar(255) not null,   `totalamount` int(11) not null,   `delivery_charg` int(11) not null,   `discount` int(11) not null,   `vat` int(11) not null,   `total_price` int(11) not null default '0',   `date_created` timestamp not null default current_timestamp on update current_timestamp,   primary key (`id`),   key `customer_id` (`customer_id`),   key `source_id` (`source_id`),   key `restaurant_id` (`restaurant_id`),   key `bill_id` (`bill_id`) ) engine=innodb  default charset=latin1 auto_increment=22 ;  -- -- dumping data table `orders` --  insert `orders` (`id`, `customer_id`, `restaurant_id`, `bill_id`, `source_id`, `order_medium_id`, `purchase_method`, `totalamount`, `delivery_charg`, `discount`, `vat`, `total_price`, `date_created`) values (1, 1, 1, 1, 1, 0, 'cash', 1600, 0, 0, 0, 1600, '2016-05-29 13:05:40'), (2, 2, 1, 2, 2, 1, 'cash', 1820, 0, 0, 0, 1820, '2016-06-27 07:21:25'), (4, 1, 1, 3, 3, 0, 'cash', 1770, 0, 0, 0, 1770, '2016-05-31 13:05:56'), (5, 3, 1, 4, 2, 1, 'cash', 1300, 0, 0, 0, 1300, '2016-06-27 07:21:31'), (6, 1, 1, 5, 1, 0, 'cash', 950, 0, 0, 0, 950, '2016-06-02 13:06:15'), (7, 1, 1, 6, 1, 0, 'cash', 1640, 0, 0, 0, 1640, '2016-06-03 13:06:24'), (8, 1, 1, 7, 2, 2, 'cash', 1600, 0, 0, 0, 1600, '2016-06-27 07:21:36'), (9, 1, 1, 8, 2, 2, 'cash', 1575, 0, 0, 0, 1575, '2016-06-27 07:21:40'), (10, 1, 1, 9, 3, 0, 'cash', 1125, 0, 0, 0, 1125, '2016-06-06 13:06:48'), (11, 1, 1, 10, 2, 3, 'cash', 1920, 0, 0, 0, 1920, '2016-06-27 07:21:51'); 

requirement :

i want segment records per customer following.

get rating on basis of last purchase customer

1. customers ordered in last 2 week give ratingflag 5 2. customers ordered between 2 weeks 4 week give ratingflag 3 3. customers ordered between 4 weeks 8 week give ratingflag 2 , on. 

get rating on basis of number of order customer

1. customer ordered more 5 in month give rating 5   2. customer ordered less 5 , more in month 4 give rating 4   , on. 

get rating on basis of total transaction customer

1. customer ordered more 5000 rs in month give rating 5   2. customer ordered less 5000 rs , more in month 4000 give rating 4   , on. 

customer should unique. write 3 different query getting records according requirement.

i tried following . there way result in single query. appreciate if me better approach of doing same :

1.) query last purchase

select o.customer_id,        (case when max(date_created) >= date_sub(now(), interval 2 week) 5              when max(date_created) >= date_sub(now(), interval 4 week) 4              when max(date_created) >= date_sub(now(), interval 8 week) 3              when max(date_created) >= date_sub(now(), interval 10 week) 2              when max(date_created) >=  date_sub(now(), interval 12 week) 1         end) rating  orders o o.restaurant_id = 1 group o.customer_id; 

output

customer_id rating 1            5 2            5 5            5 

2.) query number of order

select o.customer_id,        (case when count(bill_id) >= 6 5              when count(bill_id) >= 4  , count(bill_id) < 6 4              when count(bill_id) >= 3  , count(bill_id) < 4 3              when count(bill_id) >= 2  , count(bill_id) < 3 2              when count(bill_id) >= 1 1         end) rating orders o o.restaurant_id = 1 group o.customer_id 

output

customer_id rating 1            5 2            1 5            1 

3.) query total transaction customer

select o.customer_id,        (case when sum(total_price) >= 5000 5              when sum(total_price) >= 3000  , sum(total_price) < 5000 4              when sum(total_price) >= 2000  , sum(total_price) < 3000 3              when sum(total_price) >= 1000  , sum(total_price) < 2000 2              when sum(total_price) < 1000  1         end) rating orders o o.restaurant_id = 1 group o.customer_id 

output

customer_id rating 1            5 2            2 5            2 

expected output

customer_id      r1     r2       r3     1            5      5        5     2            5      1        2     3            5      1        2 

select o.customer_id,        (case when max(date_created) >= date_sub(now(), interval 2 week) 5              when max(date_created) >= date_sub(now(), interval 4 week) 4              when max(date_created) >= date_sub(now(), interval 8 week) 3              when max(date_created) >= date_sub(now(), interval 10 week) 2              when max(date_created) >=  date_sub(now(), interval 12 week) 1         end) rating1,          (case when count(bill_id) >= 6 5              when count(bill_id) >= 4  , count(bill_id) < 6 4              when count(bill_id) >= 3  , count(bill_id) < 4 3              when count(bill_id) >= 2  , count(bill_id) < 3 2              when count(bill_id) >= 1 1         end) rating2,         (case when sum(total_price) >= 5000 5              when sum(total_price) >= 3000  , sum(total_price) < 5000 4              when sum(total_price) >= 2000  , sum(total_price) < 3000 3              when sum(total_price) >= 1000  , sum(total_price) < 2000 2              when sum(total_price) < 1000  1         end) rating3 orders o o.restaurant_id = 1 group o.customer_id 

try this. faster above answer. no need use joins. check http://sqlfiddle.com/#!9/192b0/5