Date: 2021-09-20
Language: SQL
Code Link: Github

Background

Had a technical interview for a IT role and they asked the question below to see how a person would work through and solve the problem
Key items they were looking for:

• Ask questions about the problem and seek help for any clarity required
• Talk through with them how the problem is being solved while solving it
• At the end, explain how the problem was solved
• How efficient the solution code was and the Nth value required
• Communicatio skills
• Coding methodology and process. Whether it follows good practice or just ad-hoc style

Problem

First create the table using the below SQL.
Then find the sales who made the most sales amount for the year 2020, the result should include two columns sales_id, total_amount

CREATE TABLE sales (
  id INT,
  amount INT,
  sales_id VARCHAR(30),
  sales_date DATE
);
INSERT INTO sales VALUES (1, 150, 'Andy', '2020-01-01');
INSERT INTO sales VALUES (2, 210, 'John', '2020-01-02');
INSERT INTO sales VALUES (3, 140, 'Mike', '2020-01-03');
INSERT INTO sales VALUES (4, 70, 'Andy', '2020-01-04');
INSERT INTO sales VALUES (5, 10, 'John', '2020-01-05');
INSERT INTO sales VALUES (6, 150, 'Andy', '2021-01-01');
INSERT INTO sales VALUES (7, 100, 'Tom', '2020-01-03');

Solution

This was the initial solution below which is not good at all and what you should avoid. However, under time pressure it is important to stay cool and take things slowly. In the end if you are qualified you can succeed.

Answer

select 
  * 
from
  (
    select 
      sales_id
      ,sum(amount) as total_amount 
    from 
      sales 
    where 
      to_char(sales_date,'yyyy-01-01') = '2020-01-01' 
    group by 
      sales_id
  ) a
where 
  a.total_amount = 
    (
      select 
        max(b.total_amount) 
      from
        (
          select 
            sales_id
            ,sum(amount) as total_amount 
          from 
            sales
          where 
            to_char(sales_date,'yyyy-01-01') = '2020-01-01' 
          group by 
            sales_id
        ) b
    )