answers only about email, identifier, lower, substring, date_part, to_char

PHOTO EMBED

Tue Apr 12 2022 08:33:42 GMT+0000 (Coordinated Universal Time)

Saved by @Bambibo9799 

---email filtering
select name, split_part(name,' ',1) as firstname, split_part(name,' ',2) as lastname,
left(email,position('@' in email)-1) as emailname,
substring(email from position('@' in email)+1 for char_length(email))
from pizza_runner.tes as t 

-----
select * from pizza_runner.tes as t
where lower("name") ~ 'samantha|castor|hurley|stanley';
--where lower("name") similar to 'samantha%|%stanley'='t'
------
select *, substring(email,1,position('@'in email)-1) as emailname,
substring(email,position('@' in email)+1,char_length(email)) as emaildomain
from pizza_runner.serialtes as s 
where lower("name") ~ 'samantha|astor';
----
with phonerawnumber as(
select *, replace(phone::text,',','')
from x_csv as xc 
)
select *,concat(left(replace,3),'-',substring(replace,4,3), ' (',substring(replace,7), ')') 
from phonerawnumber 

---

select *, Initcap(left(trim(country),9))
from pizza_runner.cc_csv as cc 


--How many runners signed up for each 1 week period?
with runnersignup as (
select *,count(*),TO_CHAR( registration_date , 'W' )::integer as weekofmonth,date_part('dow',registration_date) as hari
from runners as r 
group by runner_id, registration_date
order by runner_id 
)
select *, sum(count)over(partition by weekofmonth) as totalperweek
from runnersignup
content_copyCOPY