Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table z_stats (country varchar2(3) not null, city varchar2(10) not null, abc number, datetime date not null);
create table table_name (counter_name varchar2(30), country varchar2(3), city varchar2(30),
  stddev_col_name number, cnt_wk number, counter_last_day number, var_wk number);
insert into z_stats (country, city, abc, datetime)
values ('GBR', 'London', 150, sysdate - 1);
1 rows affected

insert into z_stats (country, city, abc, datetime)
select 'GBR', 'London', 100 + level/1000, sysdate - 8
from dual
connect by level <= 10;
10 rows affected

select 'insert into table_name
select ''' || COLUMN_NAME || ''' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(' || COLUMN_NAME || ', 0)) stddev_col_name, 
    variance(nvl(' || COLUMN_NAME || ', 0)) var_wk,
    avg(nvl(' || COLUMN_NAME || ',0)) cnt_wk,
    count(*)
  from ' || OWNER || '.' || TABLE_NAME ||'
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, ''FMDAY'', ''NLS_DATE_LANGUAGE=ENGLISH'') NOT IN (''FRIDAY'', ''SATURDAY'')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(' || COLUMN_NAME || ', 0)) counter_last_day 
  from ' || OWNER || '.' || TABLE_NAME || '
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))'
from all_tab_cols where owner = user
and table_name in ('Z_STATS', 'Y_STATS', 'X_STATS')
and nullable = 'Y'
and rownum <= 10


'INSERTINTOTABLE_NAMESELECT'''||COLUMN_NAME||'''COUNTER_NAME,PRE.COUNTRY,PRE.CITY,PRE.STDDEV_COL_NAME,PRE.CNT_WK,POST.COUNTER_LAST_DAY,PRE.VAR_WKFROM(SELECTCOUNTRY,CITY,STDDEV(NVL('||COLUMN_NAME||',0))STDDEV_COL_NAME,VARIANCE(NVL('||COLUMN_NAME||',0))


insert into table_name
select 'ABC' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(ABC, 0)) stddev_col_name, 
    variance(nvl(ABC, 0)) var_wk,
    avg(nvl(ABC,0)) cnt_wk,
    count(*)
  from FIDDLE_QUMOJOIGZRQWSUZXWQVK.Z_STATS
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, 'FMDAY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('FRIDAY', 'SATURDAY')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(ABC, 0)) counter_last_day 
  from FIDDLE_QUMOJOIGZRQWSUZXWQVK.Z_STATS
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))


begin
  for r in (
    select 'insert into table_name
select ''' || COLUMN_NAME || ''' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(' || COLUMN_NAME || ', 0)) stddev_col_name, 
    variance(nvl(' || COLUMN_NAME || ', 0)) var_wk,
    avg(nvl(' || COLUMN_NAME || ',0)) cnt_wk,
    count(*)
  from ' || OWNER || '.' || TABLE_NAME ||'
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, ''FMDAY'', ''NLS_DATE_LANGUAGE=ENGLISH'') NOT IN (''FRIDAY'', ''SATURDAY'')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(' || COLUMN_NAME || ', 0)) counter_last_day 
  from ' || OWNER || '.' || TABLE_NAME || '
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))' as stmt
from all_tab_cols where owner = user
and table_name in ('Z_STATS', 'Y_STATS', 'X_STATS')
and nullable = 'Y'
and rownum <= 10
  ) loop

    dbms_output.put_line(r.stmt);
    execute immediate r.stmt;

  end loop;
end;
1 rows affected

dbms_output:
insert into table_name
select 'ABC' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(ABC, 0)) stddev_col_name, 
    variance(nvl(ABC, 0)) var_wk,
    avg(nvl(ABC,0)) cnt_wk,
    count(*)
  from FIDDLE_QUMOJOIGZRQWSUZXWQVK.Z_STATS
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, 'FMDAY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('FRIDAY', 'SATURDAY')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(ABC, 0)) counter_last_day 
  from FIDDLE_QUMOJOIGZRQWSUZXWQVK.Z_STATS
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))

select * from table_name;


COUNTER_NAME
COUNTRY
CITY
STDDEV_COL_NAME
CNT_WK
COUNTER_LAST_DAY
VAR_WK


ABC
GBR
London
15.07390919014464783395884993641978699319
104.550454545454545454545454545454545455
150
227.22273827272727272727272727272727273