Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5646ae5

Browse files
add 585
1 parentf1f0a47 commit5646ae5

File tree

2 files changed

+53
-0
lines changed

2 files changed

+53
-0
lines changed

‎README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -606,6 +606,7 @@ Your ideas/fixes/algorithms are more than welcome!
606606
|596|[Classes More Than 5 Students](https://leetcode.com/problems/classes-more-than-5-students/)|[Solution](../master/database/_596.sql)||| Easy|
607607
|595|[Big Countries](https://leetcode.com/problems/big-countries/)|[Solution](../master/database/_595.sql)| O(n)|O(1)| Easy|
608608
|586|[Customer Placing the Largest Number of Orders](https://leetcode.com/problems/customer-placing-the-largest-number-of-orders/)|[Solution](../master/database/_586.sql)||| Easy|
609+
|585|[Investments in 2016](https://leetcode.com/problems/investments-in-2016/)|[Solution](../master/database/_585.java)||| Medium|
609610
|584|[Find Customer Referee](https://leetcode.com/problems/find-customer-referee/)|[Solution](../master/database/_584.java)||| Easy|
610611
|580|[Count Student Number in Departments](https://leetcode.com/problems/count-student-number-in-departments/)|[Solution](../master/database/_580.sql) | || Medium | Left Join
611612
|577|[Employee Bonus](https://leetcode.com/problems/employee-bonus/)|[Solution](../master/database/_577.sql)||| Easy|

‎database/_585.sql

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
--585. Investments in 2016
2+
--Write a query to print the sum of all total investment values in 2016 (TIV_2016), to a scale of 2 decimal places,
3+
--for all policy holders who meet the following criteria:
4+
--
5+
--Have the same TIV_2015 value as one or more other policyholders.
6+
--Are not located in the same city as any other policyholder (i.e.: the (latitude, longitude) attribute pairs must be unique).
7+
--Input Format:
8+
--The insurance table is described as follows:
9+
--
10+
--| Column Name | Type |
11+
--|-------------|---------------|
12+
--| PID | INTEGER(11) |
13+
--| TIV_2015 | NUMERIC(15,2) |
14+
--| TIV_2016 | NUMERIC(15,2) |
15+
--| LAT | NUMERIC(5,2) |
16+
--| LON | NUMERIC(5,2) |
17+
--where PID is the policyholder's policy ID,
18+
--TIV_2015 is the total investment value in 2015, TIV_2016 is the total investment value in 2016,
19+
--LAT is the latitude of the policy holder's city, and LON is the longitude of the policy holder's city.
20+
--
21+
--Sample Input
22+
--
23+
--| PID | TIV_2015 | TIV_2016 | LAT | LON |
24+
--|-----|----------|----------|-----|-----|
25+
--| 1 | 10 | 5 | 10 | 10 |
26+
--| 2 | 20 | 20 | 20 | 20 |
27+
--| 3 | 10 | 30 | 20 | 20 |
28+
--| 4 | 10 | 40 | 40 | 40 |
29+
--Sample Output
30+
--
31+
--| TIV_2016 |
32+
--|----------|
33+
--| 45.00 |
34+
--Explanation
35+
--
36+
--The first record in the table, like the last record, meets both of the two criteria.
37+
--The TIV_2015 value '10' is as the same as the third and forth record, and its location unique.
38+
--
39+
--The second record does not meet any of the two criteria. Its TIV_2015 is not like any other policyholders.
40+
--
41+
--And its location is the same with the third record, which makes the third record fail, too.
42+
--
43+
--So, the result is the sum of TIV_2016 of the first and last record, which is 45.
44+
45+
selectsum(TIV_2016)as TIV_2016
46+
from insurance awhere
47+
(
48+
1= (selectcount(*)from insurance bwherea.LAT=b.LATanda.LON=b.LON)
49+
and
50+
1< (selectcount(*)from insurance cwherea.TIV_2015=c.TIV_2015)
51+
)
52+
;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp