1 Task 1 SQL (PostgreSQL) Files task1 2 Tests data solution.sql-postgres * test-input-01.sql x 1 -- write your code in PostgreSQL 9.4 2 SELECT exp, max, count 3 FROM assessments 4 WHERE (SELECT COUNT(DISTINCT experience) FROM assessments GROUP BY experience) as exp 5 ORDER BY exp DESC; test-input-01.sql solution.sql-post... On an online recruiting platform, each recruiting company can make a request for their candidates to complete a personalized skill assessment. The assessment can contain tasks in three categories: SQL, Algo and BugFixing. Following the assessment, the company receives a report containing, for each candidate, their declared years of experience (an integer between 0 and 100) and their score in each category. The score is the number of points from 0 to 100, or NULL, which means there was no task in this category. 6 You are given a table, assessments, with the following structure: create table assessments id integer not null, experience integer not null, sql integer, algo integer, bug_fixing integer, unique(id) ); To leave editor use Ctrl + M = Test Output Run Code Your task is to write an SQL query that, for each different length of experience, counts the number of candidates with precisely that amount of experience and how many of them got a perfect score in each category in which they were requested to solve tasks (so a NULL score is here treated as a perfect score). insert into assessments values ( 1, 3, 100, NULL, 50); insert into assessments values ( 2, 5, NULL, 100, 100); insert into assessments values ( 3, 1, 100, 100, 100); insert into assessments values ( 4, 5, 100, 50, NULL); insert into assessments values ( 5, 5, 100, 100, 100); Output (stderr): syntax error at or near "as" LINE 4: ...NCT experience) FROM assessments GROUP BY experience) as exp Your query should return a table containing the following columns: exp (each candidate's years of experience), max (number of assessments achieving the maximum score), count (total number of assessments). Rows should be ordered by decreasing exp. RUNTIME ERROR (tested program terminated with exit code 1) Detected some errors. Task 1 1 Files SQL (PostgreSQL) task1 2 maximum score), count (total number of assessments). Rows should be ordered by decreasing exp. Tests data solution.sql-postgres * test-input-01.sql x 1 -- write your code in PostgreSQL 9.4 2 SELECT exp, max, count 3 FROM assessments 4 WHERE (SELECT COUNT(DISTINCT experience) FROM assessments GROUP BY experience) as exp 5 ORDER BY exp DESC; 6 Examples: test-input-01.sql solution.sql-post... 1. Given: assessments: id | experience | sql | algo | bug_fixing 100 NULL 1 3 25 31 4 5 5 5 100 NULL 100 100 50 100 50 100 100 NULL 100 100 100 your query should return: exp | max | count To leave editor use Ctrl + M Test Output Run Code 5 3 1 2 0 1 3 1 1 2. Given: insert into assessments values ( 1, 3, 100, NULL, 50); insert into assessments values ( 2, 5, NULL, 100, 100); insert into assessments values ( 3, 1, 100, 100, 100); insert into assessments values ( 4, 5, 100, 50, NULL); insert into assessments values ( 5, 5, 100, 100, 100); assessments: id experience | sql | algo | bug_fixing Output (stderr): syntax error at or near "as" LINE 4: ... NCT experience) FROM assessments GROUP BY experience) as exp 1 2 220 37 4 | 3 52 NULL NULL 100 100 40 NULL NULL NULL 50 100 NULL 20 100 NULL 100 RUNTIME ERROR (tested program terminated with exit code 1) Detected some errors. All changes saved Any problems with the editor? Switch to basic editor Give Feedback Task 1 SQL (PostgreSQL) Files 1 task1 2 id experience | sql algo | bug_fixing - Tests data solution.sql-postgres x test-input-01.sql x 1 -- write your code in PostgreSQL 9.4 2 SELECT exp, max, count 3 FROM assessments WHERE (SELECT COUNT (DISTINCT experience) FROM assessments GROUP BY experience) as exp 5 ORDER BY exp DESC; 6 4 100 1 3 25 31 4 5 5 5 NULL 100 100 50 100 NULL 100 100 100 50 100 100 NULL 100 test-input-01.sql solution.sql-post... your query should return: exp max | count 5 2 3 1 1 3 1 1 2. Given: assessments: To leave editor use Ctrl + M id experience | sql | algo | bug_fixing Test Output Run Code 1 2 220 37 4 | 3 5 2 NULL NULL 100 100 40 NULL NULL NULL 50 100 NULL 20 100 NULL 100 insert into assessments values ( 1, 3, 100, NULL, 50); insert into assessments values ( 2, 5, NULL, 100, 100); insert into assessments values ( 3, 1, 100, 100, 100); insert into assessments values ( 4, 5, 100, 50, NULL); insert into assessments values ( 5, 5, 100, 100, 100); your query should return: 66 exp max| count Output (stderr): syntax error at or near "as" LINE 4: ... NCT experience) FROM assessments GROUP BY experience) as exp A 0 1 20 7 3 2 1 1 1 12 NPPA RUNTIME ERROR (tested program terminated with exit code 1) 1 Detected some errors. All changes saved Any problems with the editor? Switch to basic editor Give Feedback *