Hello! Help with SQL$,$ please!

We have following tables from database $\_$adventureworkslt:

company$\_$sales $($cid$,$salesamt,"year",quarter$\_$yr$,$qr$,$categoryid,ccls$,$CONSTRAINT company$\_$sales$\_$pk PRIMARY KEY $($qr$,$ cid, categoryid$))$;

plan$\_$data $($versionid$,$country,quarterid,pcid,salesamt,CONSTRAINT planapp$\_$data$\_$pkey PRIMARY KEY $($quarterid$,$ country, pcid, versionid$))$;

plan$\_$status $($quarterid$,$status,modifieddatetime,author DEFAULT CURRENT$\_$USER NOT NULL,country,CONSTRAINT plan$\_$status$\_$pk PRIMARY KEY $($quarterid$,$ country$))$;

company $($id GENERATED ALWAYS$),$cname,countrycode,city,CONSTRAINT d$\_$company$\_$pk PRIMARY KEY $($id$))$;

MATERIALIZED VIEW country$2$ TABLESPACE pg$\_$default AS SELECT DISTINCT a$.$countryregioncode AS countrycode

FROM customeraddress ca JOIN address a ON ca$.$addressid $=$ a$.$addressid WHERE ca$.$addresstype::text $=$ 'Main Office'::text WITH DATA;

Write a function in python to help managers start working with plan data.

You can call con.commit$()$ at the end of processing to apply changes to the data after you execute

SQL commands. The con variable here is a connection in psycopg$2$ library. If you do not add this

instruction, then the data will be left unmodified.

Write a function start$\_$planning$($year$,$ quarter, user, pwd$)$ where:

$$ year is the target year of the planning period,

$$ quarter is the target quarter of the planning period,

$$ user is the username of the database user,

$$ pwd is this user$$s password

These parameters have the same meaning in the next tasks.

The function should implement the following steps:

$1.$ Delete plan data from the plan$\_$data table related to the target year and quarter

$2.$ Delete all records related to the target quarter from the plan$\_$status table

$3.$ Create the necessary planning status records in the plan$\_$status table for the selected quarter. The

number of records added should be equal to the number of countries in which customercompanies $($shops$)$ are situated.

$4.$ Generate a version N of planning data in the plan$\_$data table. Use the calculation algorithm as

described in section $1\mathrm{.}4.$

$5.$ Copy the data from the version N and insert it further to the plan$\_$data table changing the version

to P

$6.$ Store the name of the user who called the function, in the records of the plan$\_$status table

If initial planning data cannot be generated for some record in plan$\_$status $($e$.$g$.,$ if no data can be

found in the company$\_$sales table$),$ then add rows with $0$ in salesamt column for related combination

of country and category.

If the function works correctly, the data will appear in the plan$\_$data and plan$\_$status tables. The N

and P versions of plan data will be created.

Populate the plan$\_$status table with the following:

Column$|$Description$|$Rules$||$

$-------$

quarterid$|$Key of planning quarter$|$String identifier of a quarter with $$YYYY$.$Q$$ value template$||$

country$|$Country of a company$|$company$.$country$||$

status$|$Planning status$|$R$||$

modifieddatetime$|$Time when the record was changed or created$|$current$\_$timestamp$||$

author$|$User who changed the record$|$current$\_$user$||$

Rules for loading data into the plan$\_$data tables are shown here:

Column$|$Description$|$Rules$||$

$-------$

versionid$|$Version of plan$|$a version of the plan: initial data generation gets N$,$ a copy from N gets P$||$

country$|$Country of a shop which orders goods$|$country$2.$countrycode or company.countrycode$||$

quarterid$|$Key of planning quarter$|$String identifier of a quarter with $$YYYY$.$Q$$ value template$||$

pcid$|$Product category$$s key$|$product$2.$pcid$||$

salesamt$|$Sales amount before taxes$|$average of total quarterly purchase volume in A and B groups calculated based on company$\_$sales.salesamt$||$

Call this function on behalf of Ivan. The target planning period is $1$st quarter of $2014.$

Section $1\mathrm{.}4$ To evaluate the expected volume of sales to shops, you need to use actual sales to customers, which

make the most of total yearly sales to companies $($not individuals$).$

Company $($shop$)$ grouping is conducted with help of ABC$-$analysis based on sales data of each year.

Only A and B groups of customers are used to prepare the initial plan data.

The planning year is further denoted as y$.$

To determine the initial target sales amount, it is necessary to calculate the quarterly sales amounts in

$($y $1)$ and $($y $2)$ years $($excluding customers in the group C$)$ based on planning period, quarter and

year of fact data, country and product category. Then calculate quarterly averages for each

combination of planning period, country and product category.

The administrator saves the initial data as an $$N$$ version of the plan. The same data is copied to the

$$P$$ version which is supposed to be edited by the managers.

For example, the calculation in the first quarter of $2020$ for Bikes in US will require data in first

quarter of $2018(2018$Q$1\_$US$\_$Bikes$)$ and the first quarter of $2019(2019$Q$1\_$US$\_$Bikes$).$ The result of

calculations will be equal to $(2018$Q$1\_$US$\_$Bikes $+2019$Q$1\_$US$\_$Bikes$)/2.$

If there were no sales in one of the two years, then do not divide the number by $2.$