SQL/SQL_data_types.py at main · mouse1500/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
#!/usr/bin/env python
# coding: utf-8

# Задание 1.
# Пользователи, сдающие квартиры на Airbnb, зарегистрировались в разное время.
# Кто-то – очень давно, а кто-то совсем недавно.
# Давайте проверим, в какой месяц и год зарегистрировалось наибольшее количество новых хостов.
# В качестве ответа введите дату следующего формата: 2010-12
SELECT
    COUNT(DISTINCT host_id) AS host,
    toStartOfMonth(toDateOrNull(host_since)) AS year_month

FROM listings

GROUP BY
    year_month

ORDER BY
    host DESC

LIMIT 100
# В качестве ответа использовать первую строку полученной таблицы


# Задание 2.
# Посмотрим на среднюю частоту ответа среди хозяев (f) и суперхозяев (t).
SELECT
    host_is_superhost,
    AVG(toInt32OrNull(replaceAll(host_response_rate, '%', ''))) as AvgResp
FROM
    (SELECT
        DISTINCT host_id,
        host_is_superhost,
        host_response_rate
    FROM listings
    ) as sub
GROUP BY
    host_is_superhost
LIMIT 10
# Ответ:
# t  - 98
# f  - 89.39845065678679


# Задание 3.
# Сгруппируйте данные из listings по хозяевам (host_id) и посчитайте,
# какую цену за ночь в среднем каждый из них устанавливает (у одного хоста может быть несколько объявлений).
# Идентификаторы сдаваемого жилья объедините в отдельный массив. Таблицу отсортируйте по убыванию средней цены и
# убыванию host_id (в таком порядке). В качестве ответа укажите первый массив в результирующей таблице,
# состоящий более чем из двух id
SELECT
    host_id,
    groupArray(id) AS gr_id,
    AVG(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) as price_avg
FROM
    listings
GROUP BY
    host_id
ORDER BY
        price_avg DESC
LIMIT 10
# Ответ:
# 25757977, 25759146, 25802565, 25802651, 25802838, 25802909, 25803050, 25803117, 25803218, 25803260


# Задание 4.
# Посчитаем разницу между максимальной и минимальной установленной ценой у каждого хозяина.
# В качестве ответа укажите идентификатор хоста, у которого разница оказалась наибольшей.
SELECT
    host_id,
    groupArray(id) AS gr_id,
    MAX(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) as price_max,
    MIN(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) as price_min,
    price_max - price_min AS difference
FROM
    listings
GROUP BY
    host_id
ORDER BY
    difference DESC
LIMIT 10
# Ответ
# 155140624


# Задание 5.
# Теперь сгруппируйте данные по типу жилья и выведите средние значения цены за ночь,
# размера депозита и цены уборки.
# Обратите внимание на тип данных, наличие значка $ и запятых в больших суммах.
# Для какого типа жилья среднее значение залога наибольшее?
SELECT
    room_type,
    price,
    AVG(toFloat32OrNull(replaceRegexpAll(security_deposit, '[$,]', ''))) as deposit,
    cleaning_fee
FROM
    listings
GROUP BY
    room_type, price, cleaning_fee
ORDER BY
    deposit DESC
LIMIT 10
# Ответ:
# Entire home/apt


# Задание 6.
# В каких частях города средняя стоимость за ночь является наиболее низкой?
# Сгруппируйте данные по neighbourhood_cleansed и посчитайте среднюю цену за ночь в каждом районе.
# В качестве ответа введите название места, где средняя стоимость за ночь ниже всего.
SELECT
    neighbourhood_cleansed,
    AVG(toFloat32OrNull(replaceRegexpAll(price, '[$,]', ''))) AS price_avg
FROM
    listings
GROUP BY
    neighbourhood_cleansed
ORDER BY
    price_avg ASC
LIMIT 10
# Ответ:
# Neu-Hohenschönhausen Süd


# Задание 7.
# В каких районах Берлина средняя площадь жилья, которое сдаётся целиком, является наибольшей?
# Отсортируйте по среднему и выберите топ-3.
SELECT
    neighbourhood_cleansed,
    AVG(toFloat32OrNull(square_feet)) AS square,
    room_type
FROM
    listings
GROUP BY
    neighbourhood_cleansed,
    room_type
ORDER BY
    square DESC
LIMIT 10
# Ответ:
# Lichtenrade, Kaulsdorf, Schöneberg-Süd


# Задание 8.
# Какая из представленных комнат расположена ближе всего к центру города.
# В качестве ответа укажите id объявления.
SELECT
    room_type,
    id,
    geoDistance(13.4050, 52.5200, toFloat64OrNull(longitude), toFloat64OrNull(latitude)) AS range_geo
FROM
    listings
GROUP BY
    room_type,
    id,
    latitude,
    longitude
HAVING room_type = 'Private room'
ORDER BY range_geo ASC
LIMIT 10
# Ответ:
# id - 19765058