CREATE OR REPLACE FUNCTION public.visitors_count_avg_visitors(date_begin timestamp with time zone, date_end timestamp with time zone, observation_points integer[])
 RETURNS TABLE("time" timestamp with time zone, y real)
 LANGUAGE plpgsql
AS $BODY$
begin
       CREATE TEMP TABLE Shops_tmp (ShopNo, timezone)  on commit drop
       AS
        SELECT "number", timezone
            FROM Shops 
            WHERE id = any($3) 	
    RETURN QUERY   
    SELECT
        CASE WHEN aux1.ticks = '00:00:00'
            THEN
            ('2018-09-02'::date)::timestamp with time zone
        ELSE
            ('2018-09-01'::date + aux1.ticks)::timestamp with time zone
        END AS "time",
        avg(tab4.counter)::real AS y
    FROM (
        SELECT
            tab3. "date",
            tab3. "time",
            avg(tab3.counter) AS counter
        FROM (
            SELECT
                tab2.shop_number,
                tab2. "date",
                tab2. "time",
                coalesce(tab1.counter / tab2.counter, 0) AS counter
            FROM (
                SELECT
                    t.shop_number,
                    (t.period_begin at time zone s.timezone)::date AS "date",
                    (to_timestamp(floor((extract('epoch' FROM (t.period_begin)) / 300)) * 300)at time zone s.timezone)::time AS "time",
                    count(d.id) AS counter
                from tasks AS t     
                    INNER JOIN detections AS d ON d.task_id = t.id
                   INNER JOIN Shops_tmp AS s ON t.shop_number = s.ShopNo
                    INNER JOIN task_processing_types AS tpt ON tpt.task_id = t.id
                where t.period_begin BETWEEN ($1 at time zone 'UTC')::date AND ($2 at time zone 'UTC')::date
                  AND d.target_id = 6
                  AND tpt.processing_type_id = 11
                GROUP BY
                    "date",
                    "time",
                    t.shop_number
                    ) AS tab1
            RIGHT JOIN (
                SELECT
                    count(t.id)::real AS counter,
                    t.shop_number,
                    (t.period_begin at time zone s.timezone)::date AS "date",
                    (to_timestamp(floor((extract('epoch' FROM (t.period_begin)) / 300)) * 300) at time zone s.timezone)::time AS "time"
                FROM
                    tasks AS t
                    INNER JOIN task_processing_types AS tpt ON tpt.task_id = t.id
                    INNER JOIN Shops_tmp AS s ON t.shop_number = s.ShopNo
                where t.period_begin BETWEEN ($1 at time zone 'UTC')::date AND ($2 at time zone 'UTC')::date
                   AND tpt.processing_type_id = 11
                GROUP BY
                    "date",
                    "time",
                    t.shop_number
                    ) AS tab2 ON tab1. "date" = tab2. "date"
                AND tab1. "time" = tab2. "time"
                AND tab1.shop_number = tab2.shop_number) AS tab3
        GROUP BY
            tab3. "date",
            tab3. "time") AS tab4
    RIGHT JOIN (
        SELECT
            generate_series(('2018-09-01 06:00:00')::timestamp, ('2018-09-02 00:00:00')::timestamp, '5minutes')::time AS ticks) AS aux1 ON tab4. "time" = aux1.ticks
GROUP BY
    aux1.ticks
ORDER BY
    aux1.ticks - interval '5minutes';
        END;
$function$