select count(id) first_cart_count,
SUM(order_count) first_order_count,
QUARTER(created) quarter_segment,
MONTH(created) month_segment,
YEAR(created) year_segment,
SUM(subtotal) subtotal,
SUM(total) total,
DATE_FORMAT(created,'%Y-%m-01') segment
FROM stat_user_first_cart
GROUP BY segment
ORDER BY segment desc;
select count(id) first_cart_count,
SUM(order_count) first_order_count,
QUARTER(created) quarter_segment,
MONTH(created) month_segment,
YEAR(created) year_segment,
SUM(subtotal) subtotal,
SUM(total) total,
DATE_FORMAT(created,'%Y-%m-01') segment
from stat_user_first_cart
group by quarter_segment, year_segment
order by year_segment desc, quarter_segment desc;
select user_id,
users.username,
users.company_id,
IF(users.company_id, companies.name, users.company) user_company,
QUARTER(stat_user_last_cart.created) quarter_segment,
MONTH(stat_user_last_cart.created) month_segment,
YEAR(stat_user_last_cart.created) year_segment,
stat_user_last_cart.total,
stat_user_last_cart.subtotal,
stat_user_last_cart.order_count,
DATE_FORMAT(stat_user_last_cart.created,'%Y-%m-01') segment
from stat_user_last_cart
left join users ON users.id = stat_user_last_cart.user_id
left join companies ON users.company_id = companies.id
where DATE_FORMAT(stat_user_last_cart.created, '%Y-%m-01') = DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 11 MONTH), '%Y-%m-01')
order by year_segment desc, quarter_segment desc;
select user_id,
users.username,
users.company_id,
IF(users.company_id, companies.name, users.company) user_company,
QUARTER(stat_user_first_cart.created) quarter_segment,
MONTH(stat_user_first_cart.created) month_segment,
YEAR(stat_user_first_cart.created) year_segment,
stat_user_first_cart.total,
stat_user_first_cart.subtotal,
stat_user_first_cart.order_count,
DATE_FORMAT(stat_user_first_cart.created,'%Y-%m-01') segment
from stat_user_first_cart
left join users ON users.id = stat_user_first_cart.user_id
left join companies ON users.company_id = companies.id
where DATE_FORMAT(stat_user_first_cart.created, '%Y-%m-01') = DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 0 MONTH), '%Y-%m-01')
order by year_segment desc, quarter_segment desc;
select count(id) active_cart_count,
SUM(total) active_cart_total,
SUM(subtotal) active_cart_subtotal,
SUM(order_count) active_cart_order_count,
DATE_FORMAT(created,'%Y-%m-01') segment
from stat_user_last_cart
where DATE_FORMAT(created, '%Y-%m-01') >= DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 12 MONTH), '%Y-%m-01') ;
Unique carts. Multiple carts placed by a single user/company count as 1
select count(segmented_carts.id) unique_user_count, segmented_carts.user_id, segment from (
select carts.id, carts.user_id, DATE_FORMAT(carts.created, '%Y-%m') segment
from carts
left join users on users.id = carts.user_id
where carts.status_id = 20 and ( users.company_id NOT IN (19,1,2) OR users.company_id IS NULL)
group by segment, CASE WHEN users.company_id IS NULL THEN user_id ELSE users.company_id END
order by segment desc) segmented_carts
group by segment
order by segment desc;
Unique Carts. Grouped by quarter instead of by month
select count(segmented_carts.id) unique_user_count,
segmented_carts.user_id,
quarter_segment,
year_segment,
month_segment
from (
select carts.id, carts.user_id,
QUARTER(carts.created) quarter_segment,
YEAR(carts.created) year_segment,
DATE_FORMAT(carts.created, '%Y-%m') month_segment
from carts
left join users on users.id = carts.user_id
where carts.status_id = 20 and ( users.company_id NOT IN (19,1,2) OR users.company_id IS NULL)
group by quarter_segment, year_segment, CASE WHEN users.company_id IS NULL THEN user_id ELSE users.company_id END
order by year_segment DESC, quarter_segment desc) segmented_carts
group by quarter_segment, year_segment
order by year_segment desc, quarter_segment DESC;
select count(carts.id), DATE_FORMAT(carts.created, '%Y-%m') segment
from carts
left join users on users.id = carts.user_id
where carts.status_id = 20 and (users.company_id NOT IN (19,1,2) OR users.company_id IS NULL)
group by segment
order by segment desc;
select count(sp.id) total, width, height, depth, MONTH(sp.created), YEAR(sp.created)
from shipment_packages sp
left join shipments on shipments.id = sp.shipment_id
where shipments.status_id = 3
group by YEAR(sp.created), MONTH(sp.created), width, height, depth;