Skip to content

SQL Queries

Jump to bottom Edit New page
Jason Demant edited this page Jan 26, 2016 · 13 revisions

Pull list of people who entered their email address into the closed or sold out screen in the app.

SELECT distinct email, reason FROM bento.CouponRequest;

Pull all orders for the night, exclude cancelled ones

select 
    o.pk_Order, o.created_at, u.email, 
    d1.`name` as main_name,
    d2.`name` as side1_name,
    d3.`name` as side2_name,
    d4.`name` as side3_name,
    d5.`name` as side4_name
from CustomerBentoBox cbb
left join Dish d1 on (cbb.fk_main = d1.pk_Dish)
left join Dish d2 on (cbb.fk_side1 = d2.pk_Dish)
left join Dish d3 on (cbb.fk_side2 = d3.pk_Dish)
left join Dish d4 on (cbb.fk_side3 = d4.pk_Dish)
left join Dish d5 on (cbb.fk_side4 = d5.pk_Dish)
left join `Order` o on (o.pk_Order = cbb.fk_Order)
left join OrderStatus os on (o.pk_Order = os.fk_Order)
left join User u on (u.pk_User = o.fk_User)
where status != 'Cancelled'
    AND cbb.created_at >= CONVERT_TZ('2015-05-29 00:00:00','America/Los_Angeles','UTC') AND 
   cbb.created_at <= CONVERT_TZ('2015-05-29 23:59:59','America/Los_Angeles','UTC');

Pull all orders (adds coupon code field)

select
    o.pk_Order, o.fk_User as 'Customer Id',
   concat(u.firstname, ' ', u.lastname) as 'Customer Name',
   u.email,
    o.created_at as order_created_at,
    o.fk_Coupon as CouponCode,
    o.street, o.city, o.state, o.zip,
    os.`status`,
   o.tax,
   o.tip,
   o.amount as 'Total'
from `Order` o
left join OrderStatus os on (o.pk_Order = os.fk_Order)
left join User u on (o.fk_User = u.pk_User)
where 
 # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    o.created_at >= CONVERT_TZ('2015-01-22 00:00:00','America/Los_Angeles','UTC') AND 
   o.created_at <= CONVERT_TZ('2015-12-26 23:59:59','America/Los_Angeles','UTC')
ORDER BY order_created_at ASC;

Get server tips within a range

SELECT 
    o.pk_Order,
   o.created_at,
   o.amount,
   o.tip,
   d.firstname, d.lastname, d.email
FROM bento.`Order` o
left join OrderStatus os on (os.fk_Order = o.pk_Order)
left join Driver d on (d.pk_Driver = os.fk_Driver)
where 
 # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    o.created_at >= CONVERT_TZ('2015-05-25 00:00:00','America/Los_Angeles','UTC') AND 
   o.created_at <= CONVERT_TZ('2015-05-29 23:59:59','America/Los_Angeles','UTC')
    AND os.status != 'Cancelled';

Pull order detail with the number of Bentos in their order & promo code

select
    o.pk_Order, o.fk_User as 'Customer Id',
    concat(u.firstname, ' ', u.lastname) as 'Customer Name',
    u.email,
    o.created_at as order_created_at,
    os.`status`,
    o.tax,
    o.tip,
    o.amount as 'Total',
    (select count(*) from CustomerBentoBox where fk_Order = o.pk_Order) as BentoCount,
    o.fk_Coupon
from `Order` o
left join OrderStatus os on (o.pk_Order = os.fk_Order)
left join User u on (o.fk_User = u.pk_User)
where 
 # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    o.created_at >= CONVERT_TZ('2015-03-09 00:00:00','America/Los_Angeles','UTC') AND 
   o.created_at <= CONVERT_TZ('2015-09-30 23:59:59','America/Los_Angeles','UTC')
ORDER BY order_created_at ASC;

Who used ‘thanksbento646’ promo code and did they order again?

select o.fk_User, u.firstname, u.lastname
    ,count(*) as num # remove to count orders
from `Order` o
left join User u on (u.pk_User = o.fk_User)
where fk_User in (select fk_User from CouponRedemption where fk_Coupon = 'thanksbento646')
group by fk_User # remove to count orders
order by num desc;

Every order detail ever -- USE WITH CAUTION!!!

call bento.Report_OrderDetails();

Every Order Summary Ever

select
    o.pk_Order, o.fk_User as 'Customer Id',
   concat(u.firstname, ' ', u.lastname) as 'Customer Name',
   u.email,
    o.created_at as order_created_at,
    o.street, o.city, o.state, o.zip,
    os.`status`,
   o.tax,
   o.tip,
   o.amount as 'Total'
from `Order` o
left join OrderStatus os on (o.pk_Order = os.fk_Order)
left join User u on (o.fk_User = u.pk_User)
where 
 # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    o.created_at >= CONVERT_TZ('2015-05-25 00:00:00','America/Los_Angeles','UTC') AND 
   o.created_at <= CONVERT_TZ('2015-05-29 23:59:59','America/Los_Angeles','UTC')
ORDER BY order_created_at ASC;

Pull NPS Data

SELECT DISTINCT
  m.order_id,
  m.created_on,
  m.email,
  ms.comment AS meal_comment,
  ms.rating AS nps
FROM dish_survey ds, dish d, meal_survey ms, meal m
WHERE ds.meal_id = m.id
AND m.id = ms.meal_id
AND ds.dish_id = d.id
AND  # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    m.created_on >= CONVERT_TZ('2015-05-23 00:00:00','America/Los_Angeles','UTC') AND 
   m.created_on <= CONVERT_TZ('2015-05-30 23:59:59','America/Los_Angeles','UTC')
ORDER BY m.created_on;

Pull All Food Feedback

SELECT
  m.order_id,
  m.created_on,
  m.email,
  d.name AS dish_name,
  d.type AS dish_type,
  ds.rating AS dish_rating,
  ds.comment AS dish_comment
FROM dish_survey ds, dish d, meal_survey ms, meal m
WHERE ds.meal_id = m.id
AND  # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    m.created_on >= CONVERT_TZ('2015-05-23 00:00:00','America/Los_Angeles','UTC') AND 
   m.created_on <= CONVERT_TZ('2015-05-30 23:59:59','America/Los_Angeles','UTC')
AND m.id = ms.meal_id
AND ds.dish_id = d.id
ORDER BY m.created_on, d.type;

Count Lunch bento boxes by day and add-ons sold

SELECT item_type, sum(qty) as TotalSold
FROM bento.OrderItem ois
left join OrderStatus os on (ois.fk_Order = os.fk_Order) 
where 
    # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    # (local time, local timezone, timezone to convert to)
    ois.created_at >= CONVERT_TZ('2016-01-05 10:00:00','America/Los_Angeles','UTC') AND 
    ois.created_at <= CONVERT_TZ('2016-01-05 15:59:59','America/Los_Angeles','UTC') 
    AND os.status != 'Cancelled' # And don't count cancelled orders
group by item_type;

Count Dinner bento boxes by day and add-ons sold

# Dinner v2: Count items by day
SELECT item_type, sum(qty) as TotalSold
FROM bento.OrderItem ois
left join OrderStatus os on (ois.fk_Order = os.fk_Order) 
where 
    # Convert from local timestamp to UTC, since that's what the DB and servers store time in
    # (local time, local timezone, timezone to convert to)
    ois.created_at >= CONVERT_TZ('2016-01-05 16:00:00','America/Los_Angeles','UTC') AND 
    ois.created_at <= CONVERT_TZ('2016-01-05 23:59:59','America/Los_Angeles','UTC') 
    AND os.status != 'Cancelled' # And don't count cancelled orders
group by item_type;

Delivery time by order

SELECT 
pk_OrderStatus,CONVERT_TZ(created_at,'UTC','America/Los_Angeles') status,
(UNIX_TIMESTAMP(updated_at)-UNIX_TIMESTAMP(created_at))/60 AS t_min

FROM bento.OrderStatus

WHERE

# Convert from local timestamp to UTC, since that's what the DB and servers store time in
    created_at >= CONVERT_TZ('2016-01-17 00:00:00','America/Los_Angeles','UTC') AND 
   created_at <= CONVERT_TZ('2016-01-23 23:59:59','America/Los_Angeles','UTC');

Sum Tips for Lunch

SELECT sum(tip) as TipsTotal
FROM bento.`Order` o
left join OrderStatus os on (os.fk_Order = o.pk_Order)
left join Driver d on (d.pk_Driver = os.fk_Driver)
where o.created_at >= CONVERT_TZ('2016-01-25 10:00:00','America/Los_Angeles','UTC') AND 
   o.created_at <= CONVERT_TZ('2016-01-25 15:59:59','America/Los_Angeles','UTC') 
    AND os.status != 'Cancelled';

Sum Tips for Dinner

SELECT sum(tip) as TipsTotal
FROM bento.`Order` o
left join OrderStatus os on (os.fk_Order = o.pk_Order)
left join Driver d on (d.pk_Driver = os.fk_Driver)
where o.created_at >= CONVERT_TZ('2016-01-25 16:00:00','America/Los_Angeles','UTC') AND 
   o.created_at <= CONVERT_TZ('2016-01-25 23:59:59','America/Los_Angeles','UTC') 
    AND os.status != 'Cancelled';
Add a custom footer
Add a custom sidebar
Clone this wiki locally