Hello! Help with SQL$,$ please!

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

CREATE TABLE company$\_$abc $($

cid int$4$ NOT NULL,

salestotal numeric NULL,

cls varchar$(1)$ NULL,

year int$4$ NOT NULL,

CONSTRAINT company$\_$abc$\_$pk PRIMARY KEY $($cid$,$ year$)$

$)$;

CREATE TABLE public.salesorderheader $($

salesorderid int$4$ NOT NULL,

revisionnumber int$2$ DEFAULT $0$ NOT NULL,

orderdate timestamp$(6)$ DEFAULT CURRENT$\_$TIMESTAMP NOT NULL,

duedate timestamp$(6)$ NOT NULL,

shipdate timestamp$(6)$ NULL,

status int$2$ DEFAULT $1$ NOT NULL,

onlineorderflag bool DEFAULT true NOT NULL,

salesordernumber varchar$(25)$ NULL,

purchaseordernumber varchar$(25)$ NULL,

accountnumber varchar$(15)$ NULL,

customerid int$4$ NOT NULL,

shiptoaddressid int$4$ NULL,

billtoaddressid int$4$ NULL,

shipmethod varchar$(50)$ NOT NULL,

creditcardapprovalcode varchar$(15)$ NULL,

subtotal numeric$(19,4)$ DEFAULT $0$ NOT NULL,

taxamt numeric$(19,4)$ DEFAULT $0$ NOT NULL,

freight numeric$(19,4)$ DEFAULT $0$ NOT NULL,

totaldue numeric$(19,4)$ NOT NULL,

"comment" varchar$(4000)$ NULL,

rowguid varchar$(36)$ NOT NULL,

modifieddate timestamp$(6)$ DEFAULT CURRENT$\_$TIMESTAMP NOT NULL,

CONSTRAINT salesorderheader$\_$pkey PRIMARY KEY $($salesorderid$)$

$)$;

ALTER TABLE public.salesorderheader ADD CONSTRAINT salesorderheader$\_$fk FOREIGN KEY $($customerid$)$ REFERENCES public.customer$($customerid$)$;

CREATE TABLE public.customer $($

customerid int$4$ NOT NULL,

namestyle bool DEFAULT false NOT NULL,

title varchar$(8)$ NULL,

firstname varchar$(50)$ NULL,

middlename varchar$(50)$ NULL,

lastname varchar$(50)$ NULL,

suffix varchar$(10)$ NULL,

companyname varchar$(128)$ NULL,

emailaddress varchar$(50)$ NULL,

rowguid varchar$(36)$ NOT NULL,

modifieddate timestamp$(6)$ DEFAULT CURRENT$\_$TIMESTAMP NOT NULL,

salespersonid int$4$ NULL,

gender varchar$(1)$ NULL,

totalchildren int$4$ NULL,

birthdate date NULL,

datefirstpurchase date NULL,

persontype varchar$(3)$ NULL,

CONSTRAINT customer$\_$pkey PRIMARY KEY $($customerid$)$

$)$;

CREATE TABLE company $($

id int$4$ NOT NULL GENERATED ALWAYS AS IDENTITY,

cname varchar$(200)$ NOT NULL,

countrycode varchar$(10)$ NULL,

city varchar$(30)$ NULL,

CONSTRAINT d$\_$company$\_$pk PRIMARY KEY $($id$)$

$)$;

Split the companies into three groups according to algorithm in section $1\mathrm{.}5($below$)$ for $2012$ and

$2013.$

Populate the $$company$\_$abc$$ table after grouping the companies. All calculations should be done in one

query. The detailed calculation rules are given below.

Field Description Rule

$-----------------------------------------------------$

cid $|$ Company key $|$Key from $$company$$ table. Find the companyname from $$customer$$ in the $$company$$ table to retrieve its key

salestotal $|$ Sales total per year $|$ Calculated as sum$($subtotal$)$ from $$salesoderheader$$ table

cls $|$ Company class $|$ The class should be calculated following an algorithm in the section $1\mathrm{.}5.$

year $|$ Year $|$ Get year of orderdate field from $$salesoderheader$$ table

Section $1\mathrm{.}5.$ Company classification by annual orders

The assessment is done quarterly in terms of product categories and the countries in which the stores

are located. In this task the planning year is $2014.$

It is necessary to split the buyer companies into three groups by their volume of orders in y $-1(2013)$

and y $-2(2012)$ years. As a result, two ratings of companies $($for each year$)$ will be created. The

following algorithm should be used to define members of the three groups for one year:

$1.$ Calculate the total volume of sales to companies before taxes. Denote the value as S

$2.$ Calculate the upper boundary values for inclusion in the groups A and B:

Sa $=0\mathrm{.}8*$ S$,$

Sb $=0\mathrm{.}95*$ S

$3.$ Sort companies in descending order by the total volume of their respective purchases made per

year $($STi$).$

$4.$ Calculate a running total of STi and denote these values as SRTi. SRTi of a single company

should be equal to the sum of the accumulated purchase volume made by this company per year

and the aggregated purchase volumes made by all the companies that are higher in the rating.

$5.$ Assign a class to eacht company $($i$)$ according to the following conditions:

a$.$ Class A $$ if SRTi does not exceed Sa$,$

b$.$ Class B $$ if SRTi does not exceed Sb$,$

c$.$ Class C $$ in any other case.