摘要：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.。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.。

“

每

日

一

練

”

May.

7

Data Application Lab 自2017年6月15日起，每天和你分享討論一道數據科學(DS)和商業分析(BA)領域常見的面試問題。

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

希望積極尋求相關領域工作的你每天關注我們的問題並且與我們一起思考，我們將會在第二天給出答案。

Day

506

DS Interview Question

What is Random Projection?

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.

LeetCode Question

linked list partition

Deion:

Given a linked list and a value x, partition it such that all nodes less than x come before nodes greater than or equal to x.

You should preserve the original relative order of the nodes in each of the two partitions.

Input: 1->4->3->2->5->2 and x = 3

Output: 1->2->2->4->3->5

Day

505

答案揭曉

DS Interview Question & Answer

Describe the basic steps to do the PCA (Principal Components Analysis)

Answer:

- Standardize the data.

- Obtain the Eigenvectors and Eigenvalues from the covariance matrix or correlation matrix, or perform Singular Vector Decomposition.

- Sort eigenvalues in descending order and choose the k eigenvectors that correspond to the k largest eigenvalues where k is the number of dimensions of the new feature subspace (k≤d).

- Construct the projection matrix W from the selected k eigenvectors.

- Transform the original dataset X via W to obtain a k-dimensional feature subspace Y.

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.

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

LeetCode Question & Answer

Word search

Deion:

Given a 2D board and a word, find if the word exists in the grid.

The word can be constructed from letters of sequentially adjacent cell, where "adjacent" cells are those horizontally or vertically neighboring. The same letter cell may not be used more than once.

Input: board = [ ['A','B','C','E'], ['S','F','C','S'], ['A','D','E','E'] ] word = "ABCCED"

Output: true

Solution:

典型的圖上的搜索題，因爲要搜索所有的組合， 所以推薦使用DFS， 利用回溯，只需要維持一個狀態

注意點

圖上的遍歷問題可以利用dx， dy數組來優化代碼

把判斷是否在邊界內寫成一個私有函數來優化代碼

遞歸的出口的選擇

Code:

Time Complexity: O(m * n)

DFS的時間複雜度是邊的個數

Space Complexity: O(m ^ 2 * n ^ 2)

判斷是否訪問過的boolean數組，worst case 可能有 n ^ 2個