ML basics(1)
Product Case(3)
ML basics(1)
Product Case(3)
1.SQL aggregation funciton
2.String functions
3.Football scores
4.Modify array
5.SQL aggregation funciton
6.SQL aggregation funciton
7.ML model basic application
8.SQL aggregation funciton
9.Job application metric
10.Simpson paradox
11.Predictive model
12.Dice questions
13.Define function
14.Experiement design
1. SQL aggregation funciton
Count of blood groups
 A blood bank maintains two tables- donor, with information about the people who are willing to donate blood and acceptor, with information about the people who are in need of blood. The banks wants to know the number of males and the number of females with a particular blood group.
 Gender(GENDER), blood group(BG), num of people with that gender and that blood group
2. String functions
How many sentences?
 Given an array of words and an array of sentences, determine which words are anagrams of each other. Calculate how many sentences can be created by replacing any word with one of the anagrams.
3. Football scores
 Football scores
 the num of goals achieved by two football teams in matches in a league is given in the form of two lists. For each match of team B, compute the total num of matches of team A where team A has scored less than or equal to the number of goals scored by team B in that match.
4. Modify array
Modify an array
 Given an array of integers, the cost to change an element is the absolute difference btw its initial value and its new value. For example, if the element is initially 10, it can be changed to 7 or 13 for a cost of 3. Determine the minimum cost to sort the array either ascending or descending along its length.
5. SQL aggregation funciton
 List customer and product without sale
 in one list return, all customers who do not have an invoice and all products that were not sold. Use the union operator in this query. Return 3 values:
 id customer.id(category=”customer”) or product.id(category=’product’)
 name customer.customer_name or product.product_name
6. SQL aggregation funciton
Goals socred by countries
 given the database of a football tournament, sort the countries by the number of goals scored by that country, descending. The number of goals scored by a country is the sum of the goals scored by its players.
 If two or more countries have the same number of goals scored, order them ascending by countries.id.
7. ML model basic application
给一个dataset,然后让去predict sales/quantity, etc。 Supervised Learning,从data pre-processing到visualization都要做
8. SQL aggregation funciton
Table name: Companies
 Columns: Year Quarter Company Revenue
 4. Use the data in the corresponding table to write a SQL query that will yield the average yearly revenue of each company between 2010 and 2015.
 5. Use the same table to write a SQL query to get the average revenue over all companies, for the first 4 quarters. Note: not all companies have the same starting date or start operation at the beginning of the year.
 6. Use the same table to write a SQL query that adds a new column to the original table: % of annual revenue accounted for by that quarter, which is defined as company revenue for that quarter-year divided by total company revenue for that year.
 以及是否要relocate,desired salary
9. Job application metric
Job application metric下降了,怎么调查原因。
10. Simpson paradox
Simpson paradox的一个问题,两种email version,连续两周在两个不同的城市做实验,显示结果是B好于A。combine起来是A好。
11. Predictive model
1 million rows, 10 K feature, someone was hired or not for a position. Predict the likelihood of a user being hired for a position.
12. Dice questions
M-sided dice, prob list, generate output
13. Define function
 f(x) = a*x**2 + b*x + c, 给一个sorted list,用发f(x) generate一个sorted output。
14. Experiement design
Feed ranking algorithm change, 怎么设计ab test