SQL

DELETE FROM tblemployee 
WHERE user_id IN
    (SELECT user_id 
    FROM 
        (SELECT user_id, name,
         		ROW_NUMBER() OVER( PARTITION BY "name" 
        		ORDER BY  user_id ) AS row_num
        FROM tblemployee ) t        
        WHERE t.row_num > 1 );

-- 1 method
select e.name, t.deptname, e.salary from
(select avg(salary) as averagesalary from tblemployee) as Budget, --first from
tblemployee as e  --second from
join tbldepartment t
on e.deptid = t.deptid
where e.salary > Budget.averagesalary;
-- 2th method
select e.name, t.deptname, e.salary from
tblemployee as e
join tbldepartment t
on e.deptid = t.deptid
where e.salary > (select avg(salary) as averagesalary from tblemployee);

-- using window partition to group by department
WITH cte_emp AS  (
    select t.name, t.deptid, t.salary, td.deptname,
        rank()OVER ( PARTITION BY t.deptid ORDER BY salary desc) highestsalaryRank
        --penggunaan rank over karena di partisi sesuai deptid, jadi hasil salary lebih                       terstruktur,
        --dan duplikat di rank over dihitung sebagai value
        --penggunaan dense_rank, maka duplikat tidak akan di anggap value baru, jadi tetap                 teroganisir secara berurutan
    FROM 
        tblemployee t
    join tbldepartment td
    on t.deptid = td.deptid
)

SELECT *
FROM cte_emp;

--find the 3rd and nth salary
select * 
from(
	select name, salary, 
	dense_rank()  over(order by salary desc)r
from tblemployee) as salarySort
where r in (2,3); -- find the salary based on the r value, sort by dense rank

Select Count(*)
From   mySchema.myTable
Where  Cast(Col1 As NVarChar(128)) +
       Cast(Col2 As NVarChar(128)) +
       Cast(Coln As NVarChar(128)) Like '%?%'

SELECT CONCAT('Q',QUARTER(`Date`), ' ',YEAR(`Date`))
FROM
  datetable
ORDER BY `Date`;

| CONCAT('Q',QUARTER(`Date`), ' ',YEAR(`Date`)) |
| --------------------------------------------- |
| Q1 2011                                       |
| Q2 2011                                       |
| Q3 2011                                       |
| Q4 2011                                       |
| Q1 2012                                       |
| Q2 2012                                       |
| Q3 2012                                       |
| Q4 2012                                       |
| Q1 2013                                       |
| Q2 2013                                       |
| Q3 2013                                       |
| Q4 2013                                       |

--------------dapatkan total sales di tiap quarter per tahun di tabel order

select *, lag(totalsales, 1) over(partition by year order by q_quarter) as previousquartersales
from( 
	select totalsales,year,concat('Q','', quarter) as Q_quarter
from(
	select round(sum(ot.sales)) as totalsales,
	extract(year from order_date)as year,
	extract(quarter from order_date) as quarter
	from orders_table as ot 
	group by 2,3
	order by 2,3 asc
	) t1
) t2



----------------
select *, lag(ss)over(partition by region,y order by y, q asc)
from(
select extract(year from order_date) as y, extract(quarter from order_date)as q, sum(sales)as ss, region 
from orders_table as ot 
group by 1,2,4
)t1

----------------(1)
CREATE TABLE "BinarPlatinum".trialaja (
	fruit varchar NULL
);
----------------------(2)
insert into trialaja (fruit)
values ('mango')
--------------------(3)
select * from trialaja as t;

------(not working without primary key, delete all) (4)
delete 
from trialaja 
where fruit in(
		select fruit
		from(
		select *,row_number()over(partition by fruit) as rn
		from trialaja as t 
		) t1
		where rn > 1
		)

select * from trialaja as t;
------------------------------------------------------------------------------------------------
---(working without pirmary key) menggunakan 

delete from trialaja 
		WHERE ctid IN (SELECT ctid
              			 FROM   (SELECT ctid, fruit,
                             	 ROW_NUMBER() OVER (
                           	     PARTITION BY fruit) AS rn
                     		 	 FROM trialaja as t) b
            		   WHERE  rn > 1);

select * from trialaja as t;

SELECT    
	first_name, 
	last_name, 
	phone, 
	REPLACE(REPLACE(phone, '(', ''), ')', '') phone_formatted
FROM    
	sales.customers
WHERE phone IS NOT NULL
ORDER BY 
	first_name, 
	last_name;

 ------create new temp table-----

 create table order_temp3 as
select row_number()over()as rn,* 
from "BinarPlatinum".orders_table;

----drop existing table
drop table "BinarPlatinum".orders_table; 

----rename new temp table into orders_table
ALTER TABLE public.order_temp3 RENAME TO orders_table;

----temukan total pembelian terbanyak berdasarkan category tiap tahun

with t1 as(
select ct.category,sum(quantity) as totalbuy,extract(year from order_date) as datee
from orders_table as ot
join categories_table as ct
on ot.product_id = ct.productid
group by 1,3
)

select *
from t1
order by datee asc, totalbuy desc;

select *
from location_csv as lc 
where lastname = ''

UPDATE location_csv 
SET lastname = NULLIF(lastname, '')

update train_employeedatabase_csv 
set yeargroup = case when age < 18 then 'Under 18'
when age between 18 and 24 then 'On 18-24'
when age between 25 and 34 then 'On 25-34'
when age > 34 then 'Over34'
end 

  update dept1
set jobrole = replace(jobrole,'Manager','Manager Sales')
where department = 'Sales';

SELECT 
  ship_country,
  SUM(CASE
    WHEN status = 'shipped' OR status = 'delivered' THEN 1
    ELSE 0
  END) AS order_shipped
FROM orders
GROUP BY ship_country;

--find each sale for each city and state based on their yearly sale, and sum them all

select *, sum(sale)over(partition by yyyy)
from(
select city,state, extract(year from order_date ) as yyyy, sum(sales)as sale
from orders_table as ot
where state = 'New York'
group by 1,2,3
)t1

select *, regexp_replace(t1, '[[:digit:]]+', '','g') -- remove all number in t1
from
(
select *, 
regexp_replace(name, '[^a-zA-Z0-9]+', '','g')as t1 -- remove all special lcharacter
,regexp_replace(number, '[^a-zA-Z0-9]+', '','g') as t2 --remove all special character
from remove_specialcharacter_csv as rsc 
) tt

----find quarter sale in each state
select *,
sum(sale)over(partition by yyyy, region order  by yyyy,qqqq rows between unbounded preceding and current row) as cummulativesum,
sum(sale)over(partition by yyyy, region) as totalyearlysale,
lag(sale, 1, 0)over(partition by region order by region, yyyy, qqqq asc) as lastyearsale,
lead(sale, 1, 0)over(partition by region order by region, yyyy, qqqq asc) as nextyearsale,
percent_rank() over(partition by region, yyyy order by qqqq asc)
from
(
select region, extract(year from order_date) as yyyy
, extract(quarter from order_date) as qqqq
, round(sum(sales)) as sale
from orders_table
group by 1, 2, 3
)t1---first
where region in ('South','West') and
yyyy in ('2016','2017')

select t3,  '( ' || SUBSTRING(CAST(t3 AS VARCHAR) FROM 1 FOR 4) || ' ) '
|| SUBSTRING(CAST(t3 AS VARCHAR) FROM 5 FOR 3) || '-' 
 || SUBSTRING(CAST(t3 AS VARCHAR) FROM 8 FOR LENGTH(CAST(t3 AS VARCHAR)))

|| to concentate between 2 strings or more

Syntax: REGEXP_REPLACE(source, pattern, replacement_string, [, flags])


select *, regexp_replace(t1, '[[:digit:]]+', '','g'),--remove all number in t1
regexp_replace(t2, '[[:alpha:]]+', '','g')as t3-- remove all alpahbets in t2
from
(
select *, 
regexp_replace(name, '[^a-zA-Z0-9]+', '','g')as t1 -- remove all special lcharacter
,regexp_replace(number, '[^a-zA-Z0-9]+', '','g') as t2 --remove all special character
from remove_specialcharacter_csv as rsc 
) tt

regexp_replace(t1, '\D', '','g')as t3, -- remove all letter in t1
regexp_replace(t2, '\d', '','g')as t5-- remove all number in t2


\d	matches any digit, like [[:digit:]] -- nomor 1-9
\s	matches any whitespace character, like [[:space:]]
\w	matches any word character, like [[:word:]]
\D	matches any non-digit, like [^[:digit:]] --- bukan nomor
\S	matches any non-whitespace character, like [^[:space:]]
\W	matches any non-word character, like [^[:word:]]

^	matches at the beginning of the string
$	matches at the end of the string
we have used Regular Expression in PostgreSQL using the TILDE (~) operator and the wildcard ‘.*’.
the expression can be implemented as “^.*$”.

                                                               
                                                               
SELECT * FROM GreaterManchesterCrime WHERE CrimeID ~ ‘^[A-Z].*$’ --A 1 UPPERCASE STRING
SELECT * FROM GreaterManchesterCrime WHERE CrimeID ~ ‘^[0–9].*$’ --0 1 NUMBER

SELECT * FROM GreaterManchesterCrime WHERE CrimeID ~ ‘^[A-Z] [A-Z].*$’ -- 2 UPPERCASE STRING
OR
SELECT * FROM GreaterManchesterCrime WHERE CrimeID ~ ‘^[A-Z] {2}.*$’ -- 2 UPPERCASE STRING

SELECT * FROM GreaterManchesterCrime WHERE CrimeID ~ ‘^[0–9]{2}.*$’ --2 NUMBER
SELECT * FROM GreaterManchesterCrime WHERE CrimeID ~ ‘^[A-Z][0–9].*$’ --  1 UPPERCASE 1 NUMBER
[A-Z][A-Z]\d\d = 2 UPPERCASE LETTER AND 2 DIGIT STRING
meaning only 2 UPPER followed by 2 digit can show up

SELECT REGEXP_REPLACE('ABC12345xyz', '[[:alpha:]]', '', 'g'); -- menghilangkan string
SELECT REGEXP_REPLACE('ABC12345xyz', '\D', '', 'g'); -- menghilangkan string

SELECT REGEXP_REPLACE('ABC12345xyz', '[[:digit:]]', '', 'g'); -- menghilangkan number
SELECT REGEXP_REPLACE('ABC12345xyz', '\d', '', 'g'); -- menghilangkan number

select regexp_replace(replace('dadjkasdkjad21312....','.',''),'[a-z]','','g')

select *, sum(sale)over(order by yyyy rows between unbounded preceding and current row)
from(
select sum(sales) as sale,extract(year from order_date)yyyy
from orders_table as ot
group by 2
order by 2 asc
)t1;

UPDATE emp_info
SET address = ''
WHERE address IS NULL;
SELECT * FROM emp_info;

UPDATE emp_info
SET salary_$ = 0
WHERE salary_$ IS Null;
SELECT * FROM emp_info;

select *,row_number()over()
insert into table1
from location_table as lt 

select
year, medals,
  sum(medals) over (order by year asc) as medal_rt
from medal_table

split_part
case when, sum case when
round
coalsece = COALESCE(Title,Suffix,FirstName) AS NewValue
lag
partition by 
lead
extract
concat('Q','',qt_sale)
concat_ws
len
charindex
ntile
rows between unpreceding bound and current rows/ preceding bound
|| 
substring
replace
rollup(column name) = GROUP BY
cube(column name) = GROUP BY - non hierical, calculate all possible aggregations
update-set, alter table-add
into
isnull, column = null
cast or using ::
wildcard % like, like 200_ (underscore wildcard), not like,
string_agg(column name, ',') put column name and delimiter, and it sorted based on the value 
lower
upper
initcap
LPAD 
ilike
substring = - SELECT substring('$15us', 2, 2)AS price;
  			- substring(email from position('@'in email)+1 for char_length(email)),
			- left(email, position('@' in email)-1)

SELECT * from Product_sales where (From_date <= '2013-01-09' AND To_date >= '2013-01-01')


SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

CREATE TABLE student(
   id SERIAl,

INSERT INTO newtable (year,medal) VALUES
	 (1992,23),
	 (1993,123),
	 (1994,12),
	 (1995,22),
	 (1997,35),
	 (1996,68);
	 
	
alter table newtable
add idtest serial primary key;

select *
from newtable as n 

TRUNCATE TABLE newtable RESTART IDENTITY; > reset to zero (the idtest primarykey)

CREATE TABLE contacts(
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50),
    age INT NOT NULL,
    active VARCHAR(10)
);

WITH contacts_json (doc) AS (VALUES(
'[
    {
        "id": 1,
        "name": "Jimmy",
        "age": 25,
        "active": "yes"
    },
    {
        "id": 2,
        "name": "Annie",
        "age": 22,
        "active": "yes"
    },
    {
        "id": 3,
        "name": "John",
        "age": 21,
        "active": "no"
    },
    {
        "id": 4,
        "name": "Mark",
        "age": 15,
        "active": "no"
    },
    {
        "id": 5,
        "name": "Shelly",
        "age": 20,
        "active": "yes"
    }
]'::json))
INSERT INTO contacts (id, name, age, active)
SELECT p.* FROM contacts_json l CROSS JOIN lateral
json_populate_recordset(NULL::contacts, doc) AS p ON conflict (id)
do UPDATE SET name = excluded.name, active = excluded.active;

select * from contacts as c 

--moving average
With US_medals as ()

select year,medals,
  avg(medals)over(order by year asc rows between 2 preceding and current row) as medasls_MA
from US_medals
order by year asc;

--moving total
With US_medals as ()

select year,medals,
  sum(medals)over(order by year asc rows between 2 preceding and current row) as medasls_MT
from US_medals
order by year asc;

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  WITH Country_Awards AS (
    SELECT
      Country,
      Year,
      COUNT(*) AS Awards
    FROM Summer_Medals
    WHERE
      Country IN ('FRA', 'GBR', 'GER')
      AND Year IN (2004, 2008, 2012)
      AND Medal = 'Gold'
    GROUP BY Country, Year)

  SELECT
    Country,
    Year,
    RANK() OVER
      (PARTITION BY Year
       ORDER BY Awards DESC) :: INTEGER AS rank
  FROM Country_Awards
  ORDER BY Country ASC, Year ASC;
-- Fill in the correct column names for the pivoted table
$$) AS ct (Country VARCHAR,
           "2004" INTEGER,
           "2008" INTEGER,
           "2012" INTEGER)

Order by Country ASC;

--using rollup
select coalesce(department,'All Department Count'),
coalesce(jobrole,'All Jobrole Count'), count(*) 
from masterdataset as m 
group by rollup(department ,jobrole) -- department is the hierachy = department > jobrole
order by department, count(*) desc 

--using cube
select coalesce(department,'All Department Count'),
coalesce(jobrole,'All Jobrole Count'), count(*) 
from masterdataset as m 
group by cube(department,jobrole)
order by department

-----------------------
select date_trunc('month', current_date) - interval '1 month' - interval '1 day'
--Which means: truncate the current date to the first day of the month, then go back 1 month and one day.

SELECT
    justify_days(INTERVAL '30 days'),
    justify_hours(INTERVAL '24 hours');
SELECT
    justify_interval(interval '1 year -1 hour');

--find which weekof month
TO_CHAR( registration_date , 'W' )::integer as weekofmonth
extract to_char as INT/String
--find hour in date
date_part('hour', order_time) as hour_ordered
date_part('dow',registration_date)  as dayofweek
-- extract date_part as INT
---
extract(dow from order_time) as dayofweek
-------------------------------------------------------------------------------------------------
--if we want to use date in one column, our row in column value cannot contain string, we can use the option nullif(column name, 'string') to replace string into NULL

PARSING DATE
DATE change text value to date column, but the string has to be in digit only
date('monday, june 10th 2014') = error because DATE only expect STRING IN DIGIT(0-9) NOT A-Z
--so we can use

TO_date(date_String, format_string) = date value
--The first argument is the string that you want to convert to a date.
-- The second one is the input format. The TO_DATE() function returns a date value.
to_Date('monday, june 10th 2014', 'Day, mm , yyyy') = DATE value
SELECT TO_DATE('20170103','YYYYMMDD') >  2017-01-03
SELECT TO_DATE('2017 Feb 20','YYYY Mon DD'); >  2017-02-20



--------------------------------------------------------------------------------
TO_CHAR(expression, format)
(1)The expression can be a timestamp, an interval, an integer, a double precision, or a numeric value that is converted to a string according to a specific format. > so its a DIGIT
(2) pattern like MM YY DD

SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy'); >  23/06/2016
SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy'); >  Jun 23, 2016
SELECT TO_CHAR( TIMESTAMP '2017-08-18 22:30:59', 'HH24:MI:SS'); > 22:30:59

	EXTRACT (YEAR FROM birth_date) AS YEAR,
	EXTRACT (MONTH FROM birth_date) AS MONTH,
	EXTRACT (DAY FROM birth_date) AS DAY

----------------------------------------------------------------------------    
now() - hire_date as diff > get interval between two date data
 
-------------------------------------------------------------------------
DATE_PART(field,source) --almost the same as extract, we getting the one value in our date
SELECT date_part('year',TIMESTAMP '2017-01-01'); > 2017
SELECT date_part('dow',TIMESTAMP '2017-03-18 10:20:30') dow,
       date_part('doy',TIMESTAMP '2017-03-18 10:20:30') doy;
 dow | doy
-----+-----
   6 |  77
-------------------------------------
date_trunc('datepart', field) --trunc a specific date into a precision time
SELECT DATE_TRUNC('hour', TIMESTAMP '2017-03-17 02:09:30'); >  2017-03-17 02:00:00
SELECT date_trunc('minute', TIMESTAMP '2017-03-17 02:09:30'); >  2017-03-17 02:09:00

substring(email from 0 for  position ('@' in email))

substring(email from position ('@' in email)+1 for char_length(email))

+1 to ignore the @ logo

LEFT(email, POSITION('@' IN email)-1)

-1 to ignore the @ logo

ALTER TABLE assets
ALTER COLUMN asset_no TYPE INT 
USING asset_no::integer;

ALTER TABLE assets 
    ALTER COLUMN location TYPE VARCHAR,
    ALTER COLUMN description TYPE VARCHAR;

alter table temp_runner_orders_v2
alter column distancefixed type float
USING distancefixed::double precision;
--
alter table temp_runner_orders_v2
alter column durationfixed  type int
USING durationfixed ::integer ;

--
ALTER TABLE temp_runner_orders_v2  
ALTER COLUMN pickuptimefixed  TYPE DATE USING to_date(pickuptimefixed , 'YYYY-MM-DD');

To exclude entries with “0”, you need to use NULLIF() with function AVG().

SELECT AVG(NULLIF(yourColumnName, 0)) AS anyAliasName FROM yourTableName;
mysql> select AVG(nullif(StudentMarks, 0)) AS Exclude0Avg from AverageDemo;

select count(*) as total
from pizza_runner.x_csv as xc
where no <> 0

select round(avg("no"),2), --count without excluding zero
 round(avg(nullif("no",0)),2) as r-- count with exluding zero
from pizza_runner.x_csv as xc 

no
2
3
4
5
56
676
43
4
5
----dataset

with t1 as(
select *
, STDDEV ("no") OVER () AS ST 
, AVG ("no") OVER ()    AS AV
from pizza_runner.zz_csv
)

select *
from t1
WHERE no BETWEEN AV - ST AND AV + ST;

CREATE TABLE pizza_runner.tes (
	id serial4 NOT NULL,
	"no" int4 NULL
);

--after that import data as usual
--only need to import the no value only, serial key will automatically generated
----truncate and alter first to restart the primary key into 1
truncate table pizza_runner.tes 

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1
ALTER SEQUENCE pizza_runner.tes_id_seq  RESTART WITH 1

---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 order_id, runner_id,
  CASE 
    WHEN pickup_time LIKE 'null' THEN ' '
    ELSE pickup_time 
    END AS pickup_time,
  CASE 
    WHEN distance LIKE 'null' THEN ' '
    WHEN distance LIKE '%km' THEN TRIM('km' from distance)--- remove km from column
    ELSE distance END AS distance,
  CASE 
    WHEN duration LIKE 'null' THEN ' ' 
    WHEN duration LIKE '%mins' THEN TRIM('mins' from duration)-- remove mins
    WHEN duration LIKE '%minute' THEN TRIM('minute' from duration)---remove minute
    WHEN duration LIKE '%minutes' THEN TRIM('minutes' from duration)--remove minutes from duration
    ELSE duration END AS duration,
  CASE 
    WHEN cancellation IS NULL or cancellation LIKE 'null' THEN ''
    ELSE cancellation END AS cancellation
--INTO temp_runner_orders
FROM runner_orders;

select *, char_length(replaced) 
from(
select *, lower(left(trim(country),9)) as replaced
from cc_csv as cc 
)t1

lower
upper
left
right
substring
trim

upper < left < trim < replace

REPLACE(REPLACE(region,'&','and'),'.','') AS character_swap_and_remove
REPLACE(TRIM(event),'-','') AS event_fixed, 

select *, trim(replace(replace(distance, 'km',''),'null','0')) as distancefixed
,left(replace(duration,'null','0'),2) as durationfixed
,replace(pickup_time,'null','') as pickuptimefixed
,coalesce(replace(cancellation,'null',''),'') as cancellationfixed
from runner_orders as ro 

select * from pizza_runner.tes as t
--use ~ to find string and use | to seperate between
where lower("name") ~ 'samantha|castor|norman|atkinson'
--use similiar to and put the =t(true) to find string, can use % 
where lower("name") similar to 'samantha%|%stanley'='t'

---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 

--- using sum case when to find the percentage of null value
select sum(case when no is null then 1 else 0 end) / Sum(1.00) as nullpercentage,
sum(case when no = 'jason' then 1 else 0 end) / Sum(1.00) as notnullpercentage
from zzzzzz_csv as zc 

rollup using subquery

select p.id, points, matches_won
from
--sum the subquery first before joining
(select id, sum(matches_won)as matches_won
from matches group by id) m
on p.id = m.id

add a file to the join statement
from points_table p
join matches_group m
on p.id = m.id and p.year = m.year

  -- Update the subquery to join on a second field
    ON c.country_id = w.country_id AND w.year = CAST(c.year AS date)
    GROUP BY w.country_id) AS subquery;

-- Add the three medal fields using one sum function
SUM(COALESCE(bronze,0) + COALESCE(silver,0) + COALESCE(gold,0)) AS medals,
SUM(COALESCE(bronze,0) + COALESCE(silver,0) + COALESCE(gold,0)) / CAST(cs.pop_in_millions AS float) AS medals_per_million

-------------------- ANY OPERATOR
SELECT
    title,
    category_id
FROM
    film
INNER JOIN film_category
        USING(film_id)
WHERE
    category_id = ANY(
        SELECT
            category_id
        FROM
            category
        WHERE
            NAME = 'Action'
            OR NAME = 'Drama'
    );


-------------- IN OPERATOR
SELECT
    title,
    category_id
FROM
    film
INNER JOIN film_category
        USING(film_id)
WHERE
    category_id IN(
        SELECT
            category_id
        FROM
            category
        WHERE
            NAME = 'Action'
            OR NAME = 'Drama'
    );

select overlay('bxxi' placing 'ud' from 2 for 2),
	trim( '             d'), trim('x' from 'xxxxxxxxddxxxxxxxxx'), 
	replace('231.21.213','.',''),
	split_part('2321.2312.3231.21321','.',4),
	concat_ws('-', 'jason', 'Alexander')

WITH trial_plan AS 
  (SELECT 
    customer_id, 
    start_date AS trial_date
  FROM foodie_fi.subscriptions
  WHERE plan_id = 0
),
-- Filter results to customers at pro annual plan = 3
annual_plan AS
  (SELECT 
    customer_id, 
    start_date AS annual_date
  FROM foodie_fi.subscriptions
  WHERE plan_id = 3
),
-- Sort values above in buckets of 12 with range of 30 days each
bins AS 
  (SELECT 
    WIDTH_BUCKET(ap.annual_date - tp.trial_date, 0, 360, 12) AS     avg_days_to_upgrade
  FROM trial_plan tp
  JOIN annual_plan ap
    ON tp.customer_id = ap.customer_id)
  
SELECT 
  ((avg_days_to_upgrade -1) * 30 || ' - ' ||   (avg_days_to_upgrade) * 30) || ' days' AS breakdown, 
  COUNT(*) AS customers
FROM bins
GROUP BY avg_days_to_upgrade
ORDER BY avg_days_to_upgrade;

select *,
point_total/games_played as point_per_game,
sum(point_total)over()/sum(games_played)over() as point_per_game_league,
round((point_total/games_played)/(sum(point_total)over()/sum(games_played::numeric)over()),2) as perfomanceindex
from game_csv as gc 

select *
from(
select distinct customer_id,
dense_rank()over( order by customer_id asc) rn_low,
dense_rank()over( order by customer_id desc) rn_high
from subscriptions as s
) t1
where rn_low <= 5 or rn_high <= 5
order by customer_id

Since a GROUP BY is included, global_gdp will require two SUM() functions.

-- Pull country_gdp by region and country
SELECT 
	region,
    country,
	SUM(gdp) AS country_gdp,
    -- Calculate the global gdp
    SUM(SUM(gdp)) OVER () AS global_gdp,
    -- Calculate percent of global gdp
    SUM(gdp) / SUM(SUM(gdp)) OVER () AS perc_global_gdp,
    -- Calculate percent of gdp relative to its region
    SUM(gdp) / SUM(SUM(gdp)) OVER (PARTITION BY region) AS perc_region_gdp


-- Bring in region, country, and gdp_per_million
SELECT 
    region,
    country,
    SUM(gdp) / SUM(pop_in_millions) AS gdp_per_million,
    -- Output the worlds gdp_per_million
    SUM(SUM(gdp)) OVER () / SUM(SUM(pop_in_millions)) OVER () AS gdp_per_million_total,
    -- Build the performance_index in the 3 lines below
    (SUM(gdp) / SUM(pop_in_millions))
    /
    (SUM(SUM(gdp)) OVER () / SUM(SUM(pop_in_millions)) OVER ()) AS performance_index

with extractedalready as(
select extract(month from order_date) as mm,
round(sum(sales::numeric)) as current_rev,
lag(round(sum(sales::numeric),0))over(order by extract(month from order_date)) as pre_rev
from orders_table as ot 
where extract(year from order_date)= 2017
group by mm
)
select *, round(current_rev/pre_rev-1,2)*100 as percentagechange 
from extractedalready



	-- Pull month and country_id
	DATE_PART('month', date) AS month,
	country_id,
    -- Pull in current month views
    SUM(views) AS month_views,
    -- Pull in last month views
    LAG(SUM(views)) OVER (PARTITION BY country_id ORDER BY DATE_PART('month', date)) AS previous_month_views,
    -- Calculate the percent change
    SUM(views) / LAG(SUM(views)) OVER (PARTITION BY country_id ORDER BY DATE_PART('month', date)) - 1 AS perc_change
FROM web_data
WHERE date <= '2018-05-31'
GROUP BY month, country_id;


In the previous exercise, you leveraged the set window of a month to calculate month-over-month changes. But sometimes, you may want to calculate a different time period, such as comparing last 7 days to the previous 7 days. To calculate a value from the last 7 days, you will need to set up a rolling calculation.

In this exercise, you will take the rolling 7 day average of views for each date and compare it to the previous 7 day average for views. This gives a clear week-over-week comparison for every single day.

SELECT
	-- Pull in date and daily_views
	date,
	SUM(views) AS daily_views,
    -- Calculate the rolling 7 day average
	AVG(SUM(views)) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
FROM web_data
GROUP BY date;

SELECT 
	-- Pull in date and weekly_avg
	date,
    weekly_avg,
    -- Output the value of weekly_avg from 7 days prior
    LAG(weekly_avg,7) OVER (ORDER BY date) AS weekly_avg_previous,
    -- Calculate percent change vs previous period
    weekly_avg / LAG(weekly_avg,7) OVER (ORDER BY date) - 1 as perc_change
FROM
  (SELECT
      -- Pull in date and daily_views
      date,
      SUM(views) AS daily_views,
      -- Calculate the rolling 7 day average
      AVG(SUM(views)) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
  FROM web_data
  -- Alias as subquery
  GROUP BY date) AS subquery
-- Order by date in descending order
ORDER BY date DESC;

SELECT
	-- Pull in region and calculate avg tallest height
    region,
    AVG(height) AS avg_tallest,
    -- Calculate region percent of world gdp
    SUM(gdp)/SUM(SUM(gdp)) OVER () AS perc_world_gdp    
FROM countries AS c
JOIN
    (SELECT 
     	-- Pull in country_id and height
        country_id, 
        height, 
        -- Number the height of each country athletes
        ROW_NUMBER() OVER (PARTITION BY country_id ORDER BY height DESC) AS row_num
    FROM winter_games AS w 
    JOIN athletes AS a ON w.athlete_id = a.id
    GROUP BY country_id, height
    -- Alias as subquery
    ORDER BY country_id, height DESC) AS subquery
ON c.id = subquery.country_id
-- Join to country_stats
JOIN country_stats AS cs 
ON cs.country_id = c.id
-- Only include the tallest height for each country
WHERE row_num = 1
GROUP BY region;

INNER JOIN - only gets the value that happened to be in both table
LEFT JOIN- get all the value from the table 1( from TABLE)
RIGHT JOIN - get all the value from the joining table (JOIN TABLE)
full join/ outer join - get all the value from both table

UNION removes duplicates, whereas UNION ALL does not.

---select rank 2,3 user that generates the most sales
select customer_id, sum(sales) as total
from orders_tab as ot 
group by customer_id 
order by total desc
offset 1 limit 2 -- skip row 1 and limit by 2

DO $$
DECLARE  
   a integer := 10;  
   b integer := 20;  
   c integer;  
BEGIN  
   c := a + b;
    RAISE NOTICE'Value of c: %', c;
END $$;

-----------------
CREATE FUNCTION getpersons() RETURNS SETOF person
   LANGUAGE plpgsql AS
$$DECLARE
   overTheAgeOf int := 15;
BEGIN
    RETURN QUERY
       SELECT *
       FROM person
       WHERE age > overTheAgeOf;
END;$$;

SELECT getpersons();

create view penjualan_kentucky_california as
select state,sales
from orders_tab as ot 
where lower(state) ~ 'kentucky|california';

select sum(sales)
from penjualan_kentucky_california

--get the cash flow sum and cummulatie sales in the state new york in 2017

with extract_mm_filter_year_and_place as (
select extract(month from order_date) as mm,
state,sales
from orders_table as ot 
where extract(year from order_date)=2017
and lower(state) = 'new york' 
)
,summed_sales as(
select state,mm,round(sum(sales)) as cashflow
from extract_mm_filter_year_and_place
group by 1,2
)
select *,sum(cashflow)over(partition by state order by mm)
from summed_sales

select sum(case when no is null then 1 else 0 end) / Sum(1.00) as nullpercentage,
sum(case when no is not null then 1 else 0 end) / Sum(1.00) as notnullpercentage
from zzzzzz_csv as zc 

--convert the phone data type into text to find specific numbers
select *, case when phone::text ilike '435%'
			or phone::text ilike '%233%'
			or phone::text ~ '23' 
			then 'yes' else 'no' end as test
from x_csv as xc 

--------------
LPAD
----------------
select lpad(name,8,'X')
select lpad('abc',8,'X')

LPAD(input_string,length, input string) prepeding text values in string
--------
SUBSTRING
--------
The following example extracts the house number (maximum 4 digits, from 0 to 9) from a string:
SELECT
	SUBSTRING (
		'The house no. is 9001',
		'([0-9]{1,4})'
	) as house_no

substring(string from start_position for length);
SUBSTRING ( string ,start_position , length )

SELECT
	SUBSTRING ('PostgreSQL' FROM 1 FOR 8); -- PostgreS
SELECT
	SUBSTRING ('PostgreSQL' FROM 8); -- SQL

------------
LENGTH(string);
--------------
SELECT
	LENGTH ('PostgreSQL Tutorial'); -- 19
SELECT
	LENGTH (''); -- 0
SELECT
	LENGTH (' '); -- 1

select *, substring(email,1,strpos(email,'@')-1) as user_name,
  substring(email,strpos(email,'@')+1,length(email)) as domain_name
from emailtraining as e; 

---------------
  LEFT
LEFT(string, n) 
--------------
SELECT LEFT(first_name, 1) initial,
    COUNT(*)
FROM customer
GROUP BY initial
ORDER BY initial;

In this example, first, the LEFT() function returns initials of all customers. Then, the GROUP BY clause groups customers by their initials. Finally, the COUNT() function returns the number of customer for each group.


---------
  REGEXP MATCHES
----------
REGEXP_MATCHES(source_string, pattern [, flags])
1) source
The source is a string that you want to extract substrings that match a regular expression.

2) pattern
The pattern is a POSIX regular expression for matching.

3) flags
The flags argument is one or more characters that control the behavior of the function. For example, (i) allows you to match case-insensitively.

SELECT 
    REGEXP_MATCHES('Learning #PostgreSQL #REGEXP_MATCHES #2123', 
         '#([A-Za-z0-9_]+)', 
        'g');

The following statement uses the REGEXP_MATCHES() function to get films whose descriptions contain the word Cat or Dog:

SELECT 
	film_id, 
	title , 
	description, 
	REGEXP_MATCHES(description, 'Cat | Dog ') cat_or_dog
FROM 
	film
ORDER BY title;

--------
REGEXP REPLACE
-----------
REGEXP_REPLACE(source, pattern, replacement_string,[, flags])   
SELECT REGEXP_REPLACE('John Doe','(.*) (.*)','\2, \1'); 

REGEXP_REPLACE(source, pattern, replacement_string,[, flags])   
1) source

The source is a string that replacement should be taken place.

2) pattern

The pattern is a POSIX regular expression for matching substrings that should be replaced.

3) replacement_string

The replacement_string is a string that to replace the substrings which match the regular expression pattern.

4) flags

The flags argument is one or more character that controls the matching behavior of the function e.g., i allows case-insensitive matching, n enables matching any character and also the newline character.
--------------------------------
*	a sequence of 0 or more matches of the atom
+	a sequence of 1 or more matches of the atom
^	matches at the beginning of the string
$	matches at the end of the string

\d	matches any digit, like [[:digit:]] -- seperti nomor 1-9
\s	matches any whitespace character, like [[:space:]]
\w	matches any word character, like [[:word:]]
\D	matches any non-digit, like [^[:digit:]] --- seperti A-Z
\S	matches any non-whitespace character, like [^[:space:]]
\W	matches any non-word character, like [^[:word:]]
--------------------------------------------------------------------------------------------
select version, regexp_replace("version",'[^0-9]+','') as number,
regexp_replace("version",'[\D]+','') as number_ver2,
regexp_replace("version",'[\d]+','') as string_ver1,
regexp_replace("version",'[^\w]+','') as tes, --selain string and number, yang lain kosong
regexp_replace("version",'[^\D]+','') as string_ver2, --selain string, yang lain kosong
regexp_replace("version",'[^\d]+','') as number_vers3, --selain number, yang lain kosong
regexp_replace("version",'[^a-zA-Z]+', '') as name -- selain aplhabet a-z A-Z, yang lain kosong
 from game_csv as gc;
----------------------------------------------------------------------------------------------
select regexp_replace('jasdoasd.asdas.,asdsa.','[^a-z]','','g');-- replace semua yang bukan huruf menjadi kosong
select regexp_replace('jasdoasd.asdas.,asdsa.','[a-z]','','g'); --replace semua huruf a-z menjadi kosong
---------------------------------------------------------------------------------------------------
SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo   
-------
 the    
 quick  
 brown  
 fox    
 jumps 
 over   
 the    
 lazy   
 dog

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo 
-----
 t         
 h         
 e         
 q         
 u         
 i         
 c         
 k         
 b         
 r         
 o         
 w         
 n         
 f         
 o         
 x         
(16 rows)

SELECT 
	summons_number, 
    CASE WHEN 
    	summons_number IN (
          SELECT 
  			summons_number 
  		  FROM 
  			parking_violation 
  		  WHERE 
            -- Match violation_time for morning values
  			violation_time SIMILAR TO '\d\d\d\dA' --4numberdigit-then start with A uppercase
    	)
        -- Value when pattern matched
        THEN 1 
        -- Value when pattern not matched
        ELSE 0 
    END AS morning 
FROM 
	parking_violation;

In this exercise, you will populate the morning column by matching patterns for violation_times occurring in the morning.
------------------------------------
pattern is ‘[^\w]+’, which means replacing everything that is not number, digit, underline with the nothing.

In the above code, the source is ‘some12-34@#$’, and the pattern is ‘\W+’ that represents all the characters except alphabets and numbers, the replacement string is a double single quote that represents nothing.

The ‘g’ is used to tell that apply this operation to all non-alphanumeric.
------------------------------------------------


SELECT 
	summons_number,
	-- Replace uppercase letters in plate_id with dash
	REGEXP_REPLACE(plate_id, '[A-Z]', '-', 'g') 
FROM 
	parking_violation;

----------FUZZYSTR MATCH--------
create extension fuzzystrmatch;
SELECT name FROM pg_available_extensions; ---display extension
select soundex('test'), soundex('tost'),difference('test','tost');
select soundex('indonesia'), soundex('indoneyssa'); --- returns the total similiarty between two string
difference('test','tost'); -- retrun the similiarity between two string from 0 - 4

---updating the table
update negara_tabel
set nama_negara = 'Indonesia'
where difference(nama_negara, 'Indonesia') = 4
------------------------------------------------------------------
EXAMPLE
--------------------------------------------------
SELECT
  summons_number,
  vehicle_color
FROM
  parking_violation
WHERE
  -- Match SOUNDEX codes of vehicle_color and 'GRAY'
  DIFFERENCE(vehicle_color, 'GRAY') = 4;

---------
UPDATE 
	parking_violation
SET 
	-- Update vehicle_color to `GRAY`
	vehicle_color = 'GRAY'
WHERE 
	summons_number IN (
      SELECT
        summons_number
      FROM
        parking_violation
      WHERE
        DIFFERENCE(vehicle_color, 'GRAY') = 4 AND
        -- Filter out records that have GR as vehicle_color
        vehicle_color != 'GR'
);
-------------------- EXAMPLE NO 2
SELECT 
	summons_number,
	vehicle_color,
    -- Include the DIFFERENCE() value for each color
	DIFFERENCE(vehicle_color, 'RED') AS "red",
	DIFFERENCE(vehicle_color, 'BLUE') AS "blue",
	DIFFERENCE(vehicle_color, 'YELOW') AS "yellow"
FROM
	parking_violation
WHERE 
	(
      	-- Condition records on DIFFERENCE() value of 4
		DIFFERENCE(vehicle_color, 'RED') = 4 OR
		DIFFERENCE(vehicle_color, 'BLUE') = 4 OR
		DIFFERENCE(vehicle_color, 'YELOW') = 4
	)
   	    -- Exclude records with 'BL' and 'BLA' vehicle colors
	) AND vehicle_color NOT SIMILAR TO 'BLA?'

------
UPDATE

UPDATE 
	parking_violation pv
SET 
	vehicle_color = CASE
      -- Complete conditions and results
      WHEN red = 4 THEN 'RED'
      WHEN blue = 4 then 'BLUE'
      WHEN yellow = 4 then 'YELLOW'
	END
FROM 
	red_blue_yellow rby
WHERE 
	rby.summons_number = pv.summons_number;

SELECT * FROM parking_violation LIMIT 1000;

---EXAMPLE 3
--
Complete the query using the regular expression pattern ' +' so that one or more consecutive space characters in the parking_held column are replaced with a single space (' ').
--
SELECT 
	LPAD(event_id, 10, '0') as event_id, 
    -- Replace consecutive spaces with a single space
    REGEXP_REPLACE(INITCAP(parking_held), ' +', ' ', 'g')  as parking_held
FROM 
    film_permit;

-----EXAMPLE 4
Write a query returning records with a registration_state that does not match two consecutive uppercase letters.
----------------------------------
SELECT
  summons_number,
  plate_id,
  registration_state
FROM
  parking_violation
WHERE
  -- Define the pattern to use for matching
  registration_state NOT SIMILAR TO '[A-Z]{2}';

Regular expressions must be enclosed in quotation marks ('').
The pattern is a regular expression, therefore, the SIMILAR TO operator is required in the WHERE clause.
As an example, to match three consecutive digits between 1 and 5, the pattern [1-5]{3} can be used.

--------------EXAMPLE 5
Write a query returning records with a vehicle_make not including an uppercase letter, a forward slash (/), or a space (\s).

SELECT
  summons_number,
  plate_id,
  vehicle_make
FROM
  parking_violation
WHERE
  -- Define the pattern to use for matching
  vehicle_make NOT SIMILAR TO '[A-Z/\s]+';

--------
CREATE TABLE T (name varchar(10));
INSERT INTO T VALUES
('John'),
('Smith'),
('John'),
('Smith'),
('Smith'),
('Tom');
--------------------first method deleting duplicate
WITH t_deleted AS
(DELETE FROM T returning *), -- 1st step
t_inserted AS
(SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk
    FROM t_deleted) -- 2nd step
INSERT INTO T SELECT name FROM t_inserted 
WHERE rnk=1; -- 3rd step 
--(here we move the search condition from the step 2 to reduce the code)

SELECT * FROM T;
-------------------------second method
select "name",row_number()over(partition by name)
into t_duplicate_fixed
from t; 

delete from t_duplicate_fixed
where row_number >1
-------------------------third method 
delete from t
where ctid in (select ctid
				from (select ctid,"name",row_number()over(partition by name)as rn from t) t2
				where rn >1) 

---------------------------------------4th method
DELETE FROM t a USING (
      SELECT MIN(ctid) as ctid, name,count(*)
        FROM t 
        GROUP BY name HAVING COUNT(*) > 1
      ) b
      WHERE a.name = b.name 
      AND a.ctid <> b.ctid	

select 
column_name,
data_type
from
information_schema.columns
where table_name = (column_name)

--this will run a query to determine the types of each table data type