高频题（0）
1. General SQL questions
| 1. How many new subscribers have we received every month starting Jan 2017?
2. How does that metric compare to the same month prior year'
3.What is the conversion rate of signups for Dec 2018?
4. There' s another table (Free Subs) with companyIDs that got a free subscription.How do we exclude them from our previous metrics？
Table:
+---------+---------+---------------+--------------+---+------+|
companyid I signupdate l subscriptiondate l terminationdate l year l month
+---------+---------+---------------+--------------+---+------+|
|13343128 | 2020-02-13 |2020-02-14 I NULL || 13343124 | 2020-02-13 I 2020-02-14 |NULL || 13343100 I 2020-02-13 I 2020-02-14 I NULL ll 13343090 | 2020-02-13 I NULL || 2020-02-13 || 13343074 I 2018-02-13 I 201 8-02-14I NULL ll 13342758 | 2018-03-23 I 2018-03-24 I NULL || 13342732 | 2018-04-05 | 2018-04-05 I NULL || 13342730 |2018-09-13 |NULL |I 2018-09-13 || 1342720 | 2017-01-05 |2017-01-06 I 2018-02- 13 || 11343128 |2017-02-08 |2017-02 -08 I 2018-08-13 || 11342610 |2017-03 -23 | 2017-04-23I 2018-11-21 || 11342720 | 2017-03-29 |2017-03-30
2. How does that metric compare to the same month prior year'
3.What is the conversion rate of signups for Dec 2018?
4. There' s another table (Free Subs) with companyIDs that got a free subscription.How do we exclude them from our previous metrics？
Table:
+---------+---------+---------------+--------------+---+------+|
companyid I signupdate l subscriptiondate l terminationdate l year l month
+---------+---------+---------------+--------------+---+------+|
|13343128 | 2020-02-13 |2020-02-14 I NULL || 13343124 | 2020-02-13 I 2020-02-14 |NULL || 13343100 I 2020-02-13 I 2020-02-14 I NULL ll 13343090 | 2020-02-13 I NULL || 2020-02-13 || 13343074 I 2018-02-13 I 201 8-02-14I NULL ll 13342758 | 2018-03-23 I 2018-03-24 I NULL || 13342732 | 2018-04-05 | 2018-04-05 I NULL || 13342730 |2018-09-13 |NULL |I 2018-09-13 || 1342720 | 2017-01-05 |2017-01-06 I 2018-02- 13 || 11343128 |2017-02-08 |2017-02 -08 I 2018-08-13 || 11342610 |2017-03 -23 | 2017-04-23I 2018-11-21 || 11342720 | 2017-03-29 |2017-03-30
2. CheckSum Follow-up
Given a 2D array, find a number (a) in each row that can be divided by another number (b) without a remainder, and return the total sum of a/b for each row. (The exact details of the follow-up question are not clear as the interviewee could not remember them precisely.)
3. CheckSum
Given a 2D array, find the difference between the maximum and minimum number in each row, and return the sum of all these differences.
4. Exclude Free Subscriptions from Metrics
Write an SQL query to update the previous metrics to exclude companyIDs that received a free subscription. Assume there is another table named 'Free_Subs' with a list of companyIDs that should be excluded.
5. Calculate Conversion Rate for December 2018
Write an SQL query to calculate the conversion rate of signups to subscriptions for December 2018. Define the conversion rate as the number of signups that converted to subscriptions divided by the total number of signups in December 2018.
