求助sql面试题 加米

求助 答案给加米题目要写一个query to summarizes account balance and number of all open (not closed) accounts by state, as of a specific date (time_stamp): november 15,2020

table: person
time_stamp [date, not null]
access_number [varchar(20).latin.not null]
name [varchar(50).latin]
age [integer]
state [varchar(10).latin]
city [varchar(50).latin]
zipcode [varchar(10).latin]

table: account
time_stamp [date, not null]
account_number [varchar(20).latin.not null]
access_number [varchar(14).latin.not null]
account_status_open_closed_code [varchar(6).latin]
account_status_code [varchar(6).latin]
acct_open_date [date]
acct_reopen_date [date]
acct_closed_date [date]
acct_balance_amount [decimal(15.3)]

题目就给了这些信息
问题1：account table里foreign key 应该是access_number还是account_number呢？
我觉得是access_number,但是account table的长度是varchar(14) 和person table里的access_number [varchar(20)]的长度不一样，没关系吧？

问题2: 请大神帮看看我写的对不对，怎么改？谢谢！
如果知道account_status_open_closed_code是怎么标注open account的，就可以直接where account_status_open_closed_code =’open' group by a.state.
但是现在没给，如果用日期来查询的话,我写的对不对?

SELECT SUM(acct_balance_amount) AS total_balance, COUNT(*)
FROM person AS a
JOIN account AS b
ON a.access_number = b.access_number
WHERE acct_open_date <='2020-11-15' AND (acct_closed_date >'2020-11-15' OR acct_closed_date IS NULL) #一直开着+15号以后才关
OR acct_reopen_date <='2020-11-15' AND acct_closed_date <'2020-11-15' #15号以前关户过，但是15号或以前又重新开户
GROUP BY a.state

求助！！所有回复都给加米呀～

1. 我觉得foreign key应该是access_number，长度没什么关系，这个14, 20只是规定最长的。
2. 关于重新开户在关户之后的情况，是不是应该改成（acct_reopen_date <='2020-11-15' AND acct_reopen_date >= acct_closed_date）
有一点不确定的是，重新开户只会有一次的情况么？如果多次，是不是意味着一个account有多条记录在表里？那是不是还要dedup?

#1 虽然长度不同，用名字一样的更靠谱
#2 open_closed_code不是实时变化的，感觉没有用。date用来判断太模糊了， 同一天先开后关和先关后开区分不出来，稍微改了一下，仅供参考
SELECT state, SUM(acct_balance_amount) AS TOTAL_BALANCE, COUNT(1) AS ACCOUNT_NUMBER
FROM account a
LEFT JOIN person p --to make sure every account has been counted
        ON a.access_number = p.access_number
WHERE (acct_open_date <= '2020-11-15' AND acct_closed_date is NULL)
        OR (acct_open_date <= '2020-11-15' AND acct_closed_date > '2020-11-15')
        OR (acct_reopen_date <= '2020-11-15' AND acct_closed_date < acct_reopen_date)
GROUP BY state;

星星不猩猩 发表于 2020-11-30 15:41:37
1. 我觉得foreign key应该是access_number，长度没什么关系，这个14, 20只是规定最长的。
2. 关于重新开户在关户之后的情况，是不是应该改成（acct_reopen_da

重现开多次 去重 说得好！我没考虑到这种corner case

去加州买保时捷 发表于 2020-11-30 15:56:27
#1 虽然长度不同，用名字一样的更靠谱
#2 open_closed_code不是实时变化的，感觉没有用。date用来判断太模糊了， 同一天先开后关和先关后开区分不出来，稍微改了一下，仅供参考
S

非常感谢！下面评论说要考虑去重 重新开关户多次的 应该怎么做呢？

睡不着的喵 发表于 2020-12-1 09:53
非常感谢！下面评论说要考虑去重 重新开关户多次的 应该怎么做呢？

同一个账户同一时间，只能有开或闭一个状态，个人理解无论是SCD Type 1 or 2，只要找到目标时间开着的就好，不会有重复的。
如果理解错误，请指正，谢谢

你好，看到你给我留言了 我没有积分回复 就在这里说了。 H1b transfer是完全没有问题的。主要是要看你之前的H1b还剩多少时间，够不够在H1b累计满五年前走到 file labor certificate这一步。 不然的话，因为PD current超过一年，后面是无法延期的，没有其他途径的话，只能离境。

你好，我有看到你给我的留言，但是我没有鳄梨回复，所以回到到你的帖子里面：看你自己啦，我觉得主要是公司Axon比较稳定，毕竟其他大公司纷纷裁员，我们不会裁员的，绿卡的效率你问问和你面试的hr，那一块儿我不太清楚，签合同前都是可以问的，可以直接问hiring manager。