每日一练 | Data Scientist & Business Analyst & Leetcode 面试题 719

Data Application Lab 自2017年6月15日起，每天和你分享讨论一道数据科学(DS)和商业分析(BA) 领域常见的面试问题。

自2017年10月4日起，每天再为大家分享一道Leetcode 算法题。

DS Interview Question

What are the Pros and Cons of Naive Bayes?

BA Interview Question

Write a query in SQL to Obtain the names of all patients whose primary care is taken by a physician who is not the head of any department and name of that physician along with their primary care physician.

Table: patient (pt)

ssn              |       name                |      address                  |  phone            | insuranceid | pcp
----------------+-------------------------+------------------------------+-------------------+----------------+--------
100000001 | John Smith             | 42 Foobar Lane          | 555-0256      |    68476213    |   1
100000002 | Grace Ritchie         | 37 Snafu Drive            | 555-0512      |    36546321    |   2
100000003 | Random J. Patient | 101 Omgbbq Street     | 555-1204      |    65465421    |   2
100000004 | Dennis Doe            | 1100 Foobaz Avenue   | 555-2048     |    68421879    |   3

Table: physician (p)

Employeeid  |     name                   |     position                                  |    ssn
------------+------------------------------+-------------------------------------------+---------------------
1         | John Dorian            | Staff Internist                              | 111111111
2         | Elliot Reid               | Attending Physician                    | 222222222
3         | Christopher Turk    | Surgical Attending Physician      | 333333333
4         | Percival Cox          | Senior Attending Physician         | 444444444
5         | Bob Kelso              | Head Chief of Medicine               | 555555555
6         | Todd Quinlan          | Surgical Attenian                        | 666666666
7         | John Wen               | Surgical Attending Physician      | 777777777
8         | Keith Dudemeister | MD Resident                               | 888888888
9         | Molly Clock             | Attending Psychiatrist                 | 999999999

LeetCode Question

Rotate Image

Description:

You are given an n x n 2D matrix representing an image.

Rotate the image by 90 degrees (clockwise).

You have to rotate the image in-place, which means you have to modify the input 2D matrix directly.

DO NOT allocate another 2D matrix and do the rotation.

Input:

[

[1,2,3],

[4,5,6],

[7,8,9]

],

Output:

[

[7,4,1],

[8,5,2],

[9,6,3]
]

DS Interview Question & Answer

What are Boosting and Stacking?

Boosting provides sequential learning of the predictors. The first predictor is learned on the whole data set, while the following are learnt on the training set based on the performance of the previous one. It starts by classifying original data set and giving equal weights to each observation. If classes are predicted incorrectly using the first learner, then it gives higher weight to the missed classified observation. Being an iterative process, it continues to add classifier learner until a limit is reached in the number of models or accuracy. Boosting has shown better predictive accuracy than bagging, but it also tends to over-fit the training data as well.

Stacking works in two phases. First, we use multiple base classifiers to predict the class. Second, a new learner is used to combine their predictions with the aim of reducing the generalization error.

BA Interview Question & Answer

Write a query in SQL to Obtain the names of all patients who had at least two appointment where the nurse who prepped the appointment was a registered nurse and the physician who has carried out primary care.

table: patient (pt)

ssn              |       name                 |      address              |  phone           | insuranceid  | pcp
-----------------+--------------------------+---------------------------+-----------------+------------------+-----
100000001 | John Smith              | 42 Foobar Lane        | 555-0256      |    68476213 |   1
100000002 | Grace Ritchie          | 37 Snafu Drive          | 555-0512      |    36546321 |   2
100000003 | Random J. Patient  | 101 Omgbbq Street   | 555-1204      |    65465421 |   2
100000004 | Dennis Doe             | 1100 Foobaz Avenue  | 555-2048     |    68421879  |  3

table: appointment (a)

appointmentid |  patient  | prepnurse | physician |    start_dt_time    |     end_dt_time     | examinationroom
---------------+-----------+-----------+-----------+---------------------+---------------------+-----------------
13216584 | 100000001 |       101 |         1 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | A
26548913 | 100000002 |       101 |         2 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | B
36549879 | 100000001 |       102 |         1 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | A
46846589 | 100000004 |       103 |         4 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | B
59871321 | 100000004 |              |         4 | 2008-04-26 10:00:00 | 2008-04-26 11:00:00 | C
69879231 | 100000003 |       103 |         2 | 2008-04-26 11:00:00 | 2008-04-26 12:00:00 | C
76983231 | 100000001 |              |         3 | 2008-04-26 12:00:00 | 2008-04-26 13:00:00 | C
86213939 | 100000004 |       102 |         9 | 2008-04-27 10:00:00 | 2008-04-21 11:00:00 | A
93216548 | 100000002 |       101 |         2 | 2008-04-27 10:00:00 | 2008-04-27 11:00:00 | B

table: physician (p)

Employeeid  |     name                   |     position                                  |    ssn
------------+------------------------------+-------------------------------------------+---------------------
1         | John Dorian            | Staff Internist                              | 111111111
2         | Elliot Reid               | Attending Physician                    | 222222222
3         | Christopher Turk    | Surgical Attending Physician      | 333333333
4         | Percival Cox          | Senior Attending Physician         | 444444444
5         | Bob Kelso              | Head Chief of Medicine               | 555555555
6         | Todd Quinlan          | Surgical Attenian                        | 666666666
7         | John Wen               | Surgical Attending Physician      | 777777777
8         | Keith Dudemeister | MD Resident                               | 888888888
9         | Molly Clock             | Attending Psychiatrist                 | 999999999

table: Nurse (n)

Employeeid     |      name             |  position       | registered |    ssn
-------------------+----------------------+-----------------+---------------+---------------

101         | Carla Espinosa   | Head Nurse | t                | 111111110
102         | Laverne Roberts | Nurse          | t                | 222222220
103         | Paul Flowers       | Nurse          | f                | 333333330

Answer:

SELECT pt.name AS "Patient",
p.name AS "Primary Physician",
n.name AS "Nurse"
FROM appointment a
JOIN patient pt ON a.patient=pt.ssn
JOIN nurse n ON a.prepnurse=n.employeeid
JOIN physician p ON pt.pcp=p.employeeid
WHERE a.patient IN
(SELECT patient
FROM appointment a
GROUP BY a.patient
HAVING count(*)>=2)
AND n.registered='true'
ORDER BY pt.name;

https://www.w3resource.com/sql-exercises/hospital-database-exercise/sql-exercise-hospital-database-38.php