Notebook
%sql DROP VIEW currentOfficers %sql CREATE OR REPLACE VIEW currentOfficers AS \ SELECT * FROM (\ SELECT *, COUNT(*) OVER (PARTITION BY company_id) \ FROM ukcompanies.officer_export WHERE inactive !='true' \ ) AS multidir; %sql ALTER TABLE currentOfficers RENAME COLUMN count TO activeOfficers;
%sql CREATE OR REPLACE VIEW officers2 AS \ SELECT * \ FROM ( SELECT *, reverse(split_part(reverse(name),' ',1)) as lastname \ FROM ukcompanies.officer_export) as tmp;
%sql CREATE OR REPLACE VIEW companies2 AS \ SELECT * FROM \ ( SELECT *, reverse(split_part(reverse(name),' ',1)) AS lastname \ FROM ukcompanies.company_export ) AS tmp;
%sql CREATE OR REPLACE VIEW officers4 AS \ SELECT * FROM officers2 WHERE lastname IN (SELECT lastname FROM ( SELECT company_type, lastname, COUNT(*) FROM companies2 \ GROUP BY company_type,lastname HAVING COUNT(*) > 20 ORDER BY company_type ) AS tmp)
%sql CREATE OR REPLACE VIEW officers3 AS \ SELECT id,company_id,name,position,start_date,end_date,date_of_birth,inactive,occupation,nationality,address,lastname,pc[1] \ FROM ( SELECT *, reverse(split_part(reverse(name),' ',1)) as lastname, \ regexp_matches(address, ' ([A_Z][A-Z]?[0-9]+[A-Z]? [0-9][A-Z]+)', 'g') as pc \ FROM ukcompanies.officer_export) as tmp;