[데이터분석] 코드카타 81~ 84

81. Invalid Tweets

 

https://leetcode.com/problems/invalid-tweets/

Write a solution to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15.

Return the result table in any order.

The result format is in the following example.

 

글자 수  세기  (Char_length)

 

select tweet_id

from tweets

where char_length(content) > 15

 

Char_length

 

 

82. Replace Employee ID with the unique identifier

https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/description/

Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.

Return the result table in any order.

The result format is in the following example.

 

select b.unique_id, a.name

from Employees a left join EmployeeUNI b on a.id=b.id

 

 

83. Product Sales Analysis I

https://leetcode.com/problems/product-sales-analysis-i/

 

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Return the resulting table in any order.

The result format is in the following example.

select b.product_name, a.year, a.price

from sales a join product b on a.product_id = b.product_id

 

 

84. customer who visited but did not make any transations

https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/

 

Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.

Return the result table sorted in any order.

The result format is in the following example.

 

transaction_id에 없는 것 구하기

 

(Wrong answer)

select a.customer_id, count(b.visit_id) 'count_no_trans'

from visits a join transactions b on a.visit_id = b.visit_id

group by 1

 

 

(Accepted)

select a.customer_id, count(a.visit_id) 'count_no_trans'

from visits a left join transactions b on a.visit_id = b.visit_id

where b.transaction_id is null

group by 1

 

아니 Null값이 table에 안 적혀 있길래 어떻게 구하는거지 했는데 그냥 is null 로 조건 걸면 되는 것이었다