特斯拉技术支持工程师实习笔试题

1.Task1

建表语句：

create table tasks ( 
    id integer not null, 
    name varchar(40) not null, 
    unique(id) 
); 
    
create table reports ( 
	id integer not null, 
    task_id integer not null, 
    candidate varchar(40) not null, 
    score integer not null, 
    unique(id) 
);

测试数据插入语句：

insert into tasks values (101, 'MinDist');
insert into tasks values (123, 'Equi');
insert into tasks values (142, 'Median');
insert into tasks values (300, 'Tricoloring');
insert into reports values (13, 101, 'John Smith', 100);
insert into reports values (24, 123, 'Delaney Lloyd', 34);
insert into reports values (37, 300, 'Monroe Jimenez', 50);
insert into reports values (49, 101, 'Stanley Price', 45);
insert into reports values (51, 142, 'Tanner Sears', 37);
insert into reports values (68, 142, 'Lara Fraser', 3);
insert into reports values (83, 300, 'Tanner Sears', 0);

题解：

SELECT reports.task_id,tasks.name AS task_name,
(case 
     when AVG(reports.score) <= 20 THEN 'Hard'
     when AVG(reports.score) > 20 and AVG(reports.score) <= 60 THEN 'Medium'
     when AVG(reports.score) > 60  THEN 'Easy'
end) as difficulty
FROM tasks
LEFT JOIN reports
ON tasks.id=reports.task_id
GROUP BY reports.task_id,tasks.name
ORDER BY reports.task_id ASC;

2.Task2

建表语句：

create table transactions ( 
    amount integer not null, 
    date date not null 
);

测试数据插入语句：

insert into transactions values ('1000', '2020-01-06');
insert into transactions values ('-10', '2020-01-14');
insert into transactions values ('-75', '2020-01-20');
insert into transactions values ('-5', '2020-01-25');
insert into transactions values ('-4', '2020-01-29');
insert into transactions values ('2000', '2020-03-10');
insert into transactions values ('-75', '2020-03-12');
insert into transactions values ('-20', '2020-03-15');
insert into transactions values ('40', '2020-03-15');
insert into transactions values ('-50', '2020-03-17');
insert into transactions values ('200', '2020-10-10');
insert into transactions values ('-200', '2020-10-10');

解题思路拆解：

-- 1.先找出每月用信用卡消费3次及以上且支出费用大于100的月份
SELECT MONTH(date) AS mon
FROM transactions
WHERE amount < 0
GROUP BY mon
HAVING COUNT(*) >= 3 AND SUM(amount) <=-100;

-- 2.再加上算出总的手续费的逻辑
SELECT (12-COUNT(t2.mon))*5 AS fee 
FROM(
	-- 1.找出每月用信用卡消费3次及以上且支出费用大于100的月份
	SELECT MONTH(date) AS mon
	FROM transactions AS t1
	WHERE amount < 0
	GROUP BY mon
	HAVING COUNT(*) >= 3 AND SUM(amount) <=-100
) AS t2;

题解：

SELECT t.total-t3.fee AS balance 
FROM(
-- 2.再加上算出总的手续费的逻辑
	SELECT (12-COUNT(t2.mon))*5 AS fee 
	FROM(
		-- 1.找出每月用信用卡消费3次及以上且支出费用大于100的月份
		SELECT MONTH(date) AS mon
		FROM transactions AS t1
		WHERE amount < 0
		GROUP BY mon
		HAVING COUNT(*) >= 3 AND SUM(amount) <=-100
	) AS t2
) AS t3
-- 3.再算一下总额
,(SELECT SUM(amount) AS total FROM transactions) AS t;

3.Task3

题解：

class Solution{
public int solution(String S) {
    int B=0;
    int A=0;
    int L=0;
    int O=0;
    int N=0;
    for(int i=0;i<S.length();i++)
    {
        if(S.charAt(i)=='B')
        {
            B++;
        }
        if(S.charAt(i)=='A')
        {
            A++;
        }
        if(S.charAt(i)=='L')
        {
            L++;
        }
        if(S.charAt(i)=='O')
        {
            O++;
        }
        if(S.charAt(i)=='N')
        {
            N++;
        }
    }
    int count=0;
    if(L < 2 || O < 2 || A < 1 || B < 1 || N < 1)
    {
        return 0;
    }
    int a=Math.min(A,B);
    int b=Math.min(a,N);
    int min=Math.min(L,O);
    count =min/2;
    if(count > b)
    {
        count=b;
    }
    return count;
 }
}

4.Task4

题解：

class Solution {
    public String solution(String S){
        String[] texts = S.split("\\n");
        StringBuffer result = new StringBuffer();
        for (String s : texts) {
            if (s !=null && s.length() >0) {
                int count = s.replace("NULL","").replace(",", "").length();
                if(!(count == 0 || containNULL(s))) {
                    result.append(s + "\n");
                }
            }
        }
        return result.substring(0,result.length()-1);
    }
    
    public boolean containNULL(String s){
        String[] text = s.split(",");
        for (String string: text) {
            if (string.equals("NULL")){
                return true;
            }
        }
        return false;
    }
    
}

注：此题解正确性通过，但性能只有33/100。