LC Python 刷题笔记 有志者事竟成

Q7. Limitations of VLOOKUP

One major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right hand side. You cannot use the standard VLOOKUP to look at the columns and the rows to find an exact match.


VLOOKUP & HLOOKUP for Dynamic Lookups

“V” in VLOOKUP refers to vertical and “H” in HLOOKUP refers to horizontal.

If the data in your table has column headers, use the VLOOKUP function. If the data table has row headers and you need to search horizontally across the rows for a “match,” then use HLOOKUP. The main limitation is that the value looked with VLOOKUP needs to be in the left-most table column and in HLOOKUP it has to be in the top row of the data set.

INDEX-MATCH is much more flexible than Excel's "lookup" functions.

Q8. How to identify duplicates in Excel

To search for duplicate rows with 1st occurrences, use this formula:
=IF(COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2,$C$2:$C$8,$C2)>1, "Duplicate row", "")

隐藏菜单里的比较 INSANE/HARD SQL 题

# 1880

1. WITH CTE AS (   
   
2.       SELECT
   
3.           driver_name,
   
4.           date,
   
5.           SUM(miles_logged) miles,
   
6.           SUM(1) AS inspections
   
7.       FROM inspections
   
8.       GROUP BY driver_name,date
   
9.       WITH ROLLUP )
   
10. 
    
11. SELECT
    
12.   CONCAT(
    
13.         if(date,concat('  date: ' , date, '; miles covered: '),
    
14.             if(!driver_name,concat(' Name: ', driver_name,'; number of inspections: ', inspections,'; miles driven: '),' Total miles driven by all drivers combined: ')
    
15.                  ),
    
16.           miles) AS summary              
    
17. FROM CTE
    
18. ORDER BY driver_name,date

复制代码

CONCAT; GROUP_CONCAT; ARRAY_AGG

1. SELECT
   
2.   CONCAT('date: ' ,start_date,'; miles covered: ',budget) TITLE
   
3. FROM projects
   
4. ORDER BY start_date ASC;
   
5. 
   
6. 
   
7. SELECT ARRAY_AGG(start_date ORDER BY start_date DESC) DATE
   
8. FROM projects
   
9. GROUP BY EXTRACT(YEAR FROM start_date)
   

复制代码

Given the time allocated to a user's tasks and their daily workingHours, return the minimum number of days necessary for them to complete the given tasks. If there's no way to tackle all of them, return -1 instead.

workingHours = 15

tasks = [4, 15, 6, 3, 13, 4, 7, 14, 8, 15, 5, 8]

Expected Output: 7

DAY1： 15
DAY2： 15
DAY3： 14
DAY4： 13
DAY5： 8 + 7
DAY6：  8 + 4 + 3
DAY7：  6 + 5 + 4


For workingHours = 3 and tasks = [2, 2, 2], the output should be: tasksScheduling(workingHours, tasks) = 3.

DAY1： 2
DAY2： 2
DAY3： 2

Carryover is not allowed