求大神解答一道数据分析题

catherineff

求助大神解答一道Excel数据分析的题，问题解释和excel数据都在附件里！ 跪谢跪谢
问题如下

1.     Usingthe Restaurant Data in the attached workbook please analyze the impact of theintroduction of the Presto device.

a.     Astrong answer will compare restaurant variables before and after theintroduction of the Presto device. Comparisons should also be made using thecontrol locations that never had the devices installed.  

Additionally,E la Carte makes money from selling games and is interested in being able toaccurately predict our revenue.  

1.     Usingthe same dataset, create a predictive model of Game Revenue based on variablesyou may think are appropriate. Please include statistical support for yourmodel.

hhjjpp

附件根本就无法解压！Presto是地名吧？

catherineff

问题是 SQL Test
Please identify what the attached SQL query (query.txt) is gathering and aggregating. Generate a final table with the columns labeled with the original source. In addition, please identify places that could be improved for SQL performance.

query如下
select waiters.id as waiter_id,
            waiters.first_name as first_name,
            waiters.last_name as last_name,
            waiters.personal_id as personal_id,
            coalesce(payment_table.total_sum,0) as total_payments,
            coalesce(tip_table.tip,0) as total_tips,
            coalesce(tip_table.percentage,0) as tip_percent,
            coalesce(waiter_table.num_tables,0) as num_tables,
            coalesce(payment_table.payments,0) as num_payments,
            coalesce(items_table.num_items, 0) as num_items,
            coalesce(payment_table.rating, 0) as rating,
            coalesce(games_table.count, 0) as num_games,
            coalesce(tip_table.total_sum,0) as total_tip_payments,
            coalesce(emails_table.num_emails,0) as num_emails,
            coalesce(payment_table.turn_time,0) as turn_time,
            coalesce(features_table.revenue,0) as game_revenue
        from waiters
        inner join restaurants on restaurants.id = waiters.restaurant_id
        left outer join (
            select
                count(*) as num_tables
            from (
                select distinct on(date(last_updated), pos_check_number)
                    *
                from piston_check
                where last_updated between %s and %s
                and waiter_id = %s
                order by date(last_updated), pos_check_number, last_updated desc
            ) as moo
        ) as waiter_table on true
        left outer join(
            SELECT
                coalesce(sum(total),0) as total_sum,
                count(*) as payments,
                avg(turn_time) as turn_time,
                avg(star_rating) as rating
            FROM piston_payment
            INNER JOIN piston_check ON piston_payment.check_id = piston_check.id
            where piston_payment.last_updated between %s and %s
            AND piston_payment.tender_type != 'cash'
            and piston_check.waiter_id = %s
        ) as payment_table on true
        left outer join(
            select
                coalesce(SUM(tip),0) as tip,
                coalesce(sum(total),0) as total_sum,
                case when (sum(total) - sum(tip) = 0 or sum(tip) = 0) then 0 else sum(tip) / (sum(total) - sum(tip)) *100 end as percentage
            from (
                SELECT piston_check.waiter_id as waiter_id,
                    sum(tip) as tip,
                    sum(total) as total
                FROM piston_payment
                INNER JOIN piston_check ON piston_payment.check_id = piston_check.id
                where piston_payment.last_updated between %s and %s
                AND piston_payment.tender_type != 'cash'
                and piston_check.waiter_id = %s
                group by date(piston_check.last_updated at time zone 'UTC' - interval '11 hours'), piston_check.pos_check_number, waiter_id
            ) as moo
            where case when total-tip=0 or tip=0 then 0 else tip end > 0
        ) as tip_table on true
        left outer join (
            select count(*) as num_items
            from piston_order_item
            inner join piston_order on piston_order_item.order_id = piston_order.id
            inner join piston_check on piston_order.check_id = piston_check.id
            where piston_order.last_updated between %s and %s
            and piston_order_item.item_name != 'Presto Games'
            and piston_order_item.parent_order_item_id is null
            and piston_check.waiter_id = %s
        ) as items_table on true
        left outer join (
            select
                count (*) as count
            from piston_game
            inner join piston_check on piston_check.id = piston_game.check_id
            where piston_game.last_updated between %s and %s
            and piston_check.waiter_id = %s
        ) as games_table on true
        left outer join(
            select count(*) as num_emails
            from piston_email
            inner join piston_check on check_id = piston_check.id
            where piston_email.last_updated between %s and %s
            and piston_check.waiter_id = %s
            and email ~* '^[A-Za-z0-9._%%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'
            and not email ~* 'elacarte.com$'
            and joined_club=true
        ) as emails_table on true
        left outer join (
            select sum(charge) as revenue
            from piston_game_feature
            inner join piston_game on game_id = piston_game.id
            inner join piston_check on piston_check.id = piston_game.check_id
            where piston_game.last_updated between %s and %s
            and piston_check.waiter_id = %s
        ) as features_table on true
        where restaurants.code = %s
        and waiters.id = %s

求解答啊