SQL面试测试

Question

作为面试的一部分，我被问到以下问题，我很想知道最佳解决方案。该问题已被编辑，因此无法识别。

问题

“交易”表具有以下结构:

create table transactions (
        sent_from varchar not null,
        receiver varchar not null,
        date date not null,
        usd_value integer not null);

编写一个查询，返回在最多 3 笔交易中至少收到 1024 美元的收款人列表。该帐户可以有 3 个以上的转账，只要 3 个或更少的交易 usd_value 总计至少 1024 美元。该表应按名称排序(升序)。

示例，给定数据:

sent_from   | receiver     | date       | usd_value 
------------+--------------+------------+--------
Jonas       | Willhelm     | 2000-01-01 | 200
Jonas       | Timpson      | 2002-09-27 | 1024
Jonas       | Bjorn        | 2001-03-16 | 512
Willhelm    | Bjorn        | 2010-12-17 | 100
Willhelm    | Bjorn        | 2004-03-22 | 10
Brown       | Bjorn        | 2013_03_20 | 500
Bjorn       | Willhelm     | 2007-06-02 | 400
Bjorn       | Willhelm     | 2001-03-16 | 400
Bjorn       | Willhelm     | 2001-03-16 | 200

查询应返回以下行集:

  account_name
 --------------
  Bjorn
  Taylor

Bjorn 账户被列出是因为它在以下三笔交易中收到了 1112 美元 512 美元 + 100 美元 + 500 美元 = 1112 美元。仅一次转账，Timpson 账户就收到了 1024 美元。 Willhelm 账户在四笔交易中收到了 1200 美元，但它没有被列出，因为该账户的三笔交易总计没有至少 1024 美元。

我的解决方案:

WITH ordered_transactions AS (
  SELECT
    receiver, usd_value,
    ROW_NUMBER()
    OVER (PARTITION BY
      receiver
      ORDER BY usd_value DESC) AS Row_ID
  FROM public.transactions
)

SELECT receiver FROM
  (SELECT receiver, sum(usd_value) as smount
  FROM ordered_transactions
  WHERE Row_ID < 4
  GROUP BY receiver) AS reduced
WHERE reduced.smount >= 1024
ORDER BY reduced.receiver ASC;

设置数据(postgreSQL):

-- 我试图在 www.sqlfiddle.com 中设置 - http://sqlfiddle.com/#!15/13fc3/3

create table transactions (
    sent_from    VARCHAR NOT NULL,
    receiver VARCHAR NOT NULL,
    date      DATE    NOT NULL,
    usd_value    INTEGER NOT NULL);


insert into transactions VALUES ('Jonas', 'Willhelm', to_date('2000-01-01', 'YYYY-MM-DD'), 200 );
insert into transactions VALUES ('Jonas', 'Taylor', to_date('2002-09-27', 'YYYY-MM-DD'), 1024 );
insert into transactions VALUES ('Jonas', 'Bjorn', to_date('2001-03-16', 'YYYY-MM-DD'), 512 );
insert into transactions VALUES ('Willhelm', 'Bjorn', to_date('2010-12-17', 'YYYY-MM-DD'), 100 );
insert into transactions VALUES ('Willhelm', 'Bjorn', to_date('2004-03-22', 'YYYY-MM-DD'), 10 );
insert into transactions VALUES ('Brown', 'Bjorn', to_date('2013-03-20', 'YYYY-MM-DD'), 500 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2007-06-02', 'YYYY-MM-DD'), 400 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2001-03-16', 'YYYY-MM-DD'), 400 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2001-03-16', 'YYYY-MM-DD'), 200 );

---

非常感谢任何关于我应该如何处理这个问题的提示。

Answer 1

您的解决方案看起来不错，但您可以使用 having 子句来简化查询:

SELECT receiver
FROM ordered_transactions
WHERE Row_ID < 4
GROUP BY receiver
HAVING SUM(usd_value) >= 1024
ORDER BY receiver ASC;