Back

Answered by battlethechamp

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;

Answered by Pfwd44

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;

Answered by tymp

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;

Answered by ran428

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;

Answered by sbw0125

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;

Answered by tester

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;

Answered by anonymousUser

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;

Answered by jrz1977

select a.*, round((100 * avg_tip) / avg_bill, 2) as tip_pct from (
select 
	case
    	when bill between 0 and 19.99 then '0-19.99'
       	when bill between 20 and 39.99 then '20-39.99'
        when bill between 40 and 59.99 then '40-59.99'
        when bill between 60 and 79.99 then '60-79.99'
        when bill between 80 and 99.99 then '80-99.99'
        when bill between 100 and 119.99 then '100-119.99'
        when bill between 120 and 139.99 then '120-139.99'
        when bill between 140 and 159.99 then '140-159.99'
        else '160+'
    end as bill_range,
    round(avg (bill), 2) as avg_bill,
    round(avg (tip), 2) as avg_tip,
    count(*) as num_samples
from tips_data group by bill_range) a
order by tip_pct desc;