Assignment 13: General SQL Recap
Use the data from the sqlite online emulation (brokerage.db). Solve the tasks, programming all the statements in one query (which will return a table for every SELECT statement). Export the query as an .sql file (name it according to the requirements fak_number_name_assign_13.sql). Submit the .sql file and input the answers in the corresponding field number in the form: https://forms.gle/A3EtdSCNzaYw4oYa8
1) Create a report where the first column is Country of Residence and the 2nd and 3rd columns are: the number of active positions and the total position amounts. Order the countries by ascending order. Exclude the NULL values.
2) Using the query from previous task as a subquery using WITH clause and generate a query output a table by countries and average amount per one position ratio (total position amount / number of positions) and name this column as: AvgAmountPerPosition. Please order the result from highest to lowest by the new column.
The code for submition should contain 2 queries: first - the initial report from Task 1 and second query from Task 2 using clause WITH.