ПО @bigdata_postupashki
Forwarded from Поступашки - ШАД, Стажировки и Магистратура
Forwarded from Поступашки - ШАД, Стажировки и Магистратура
🔵Разбор контеста стажировки в Яндекс
🔵ДЗ и пет-проект с обратной связью от преподавателя
🔵Банк заданий и вопросов с собеседований в BigTech
🔵Пробный тех.собес с обратной связью от эксперта
🔵Поддержку на всех этапах отбора: от составления резюме до испытательного срока
select count(distinct student_id)
from (
select student_id
from enrollments
where status = 'completed'
) t
select s.city
from students s
join enrollments e on s.student_id = e.student_id
where status = 'completed'
group by city
order by sum(e.progress_pct) desc, s.city asc
limit 1;
select round(avg(rating)::numeric, 2)
from reviews;
with cte_1 as (
select course, student_id, count(student_id) as cnt
from enrollments
where status = 'completed'
group by course, student_id
), cte_2 as (
select course, sum(case when cnt >= 2 then 1 else 0 end) as repeat_students, sum(cnt) as completed
from cte_1
group by course
)
select course
from cte_2
order by repeat_students desc, completed desc, course asc
limit 1;
spark.conf.set("spark.sql.join.preferSortMergeJoin", True)spark.conf.set("spark.sql.shuffle.partitions", 400)sales.hint("skew").join(customers, "customer_id")sales.repartition("customer_id") \
.sortWithinPartitions("customer_id") \
.write.mode("overwrite").parquet("/data/sales_sorted")print(result.explain(True))
*(3) SortMergeJoin [customer_id#...], [customer_id#...], Inner
SELECT o.*
FROM order AS o
WHERE o.customer_id = 42;
WITH ranked AS (
SELECT
ol.*,
ROW_NUMBER() OVER (PARTITION BY ol.order_id ORDER BY ol.price DESC) AS rn
FROM order_line AS ol
)
SELECT *
FROM ranked
WHERE rn = 2;
import org.apache.spark.sql.functions._
val orders = spark.table("order")
val address = spark.table("address")
val result = orders
.join(
address,
Seq("position_lat", "position_lon"),
"inner"
)
.select(orders("customer_id"), address("address_line"))
.distinct()
result.show()
SELECT o.user_id, o.order_id, u.region
FROM postgres.sales.orders o
JOIN iceberg.analytics.users u
ON o.user_id = u.user_id
WHERE u.region = 'eu-west'
df = spark.read.parquet("s3://...").filter("status = 'active'")df = df.select("user_id", "amount")df = spark.read.parquet("s3://events/").filter("event_date = '2025-07-30'")spark.sql("SELECT id, date FROM table WHERE date = '20250801'")Forwarded from Поступашки - ШАД, Стажировки и Магистратура
a = [1, 3, 5]
b = [2, 4, 6]
target = 7
(0, 1)
(1, 0)
(2, 2)
SELECT t1.name
FROM (
SELECT b.buyer_id, b.name, SUM(o.quantity * pr.price) as total_sum
from buyers b
JOIN orders o on b.buyer_id = o.buyer_id
JOIN products pr on o.product_id = pr.product_id
GROUP by b.buyer_id, b.name
ORDER BY total_sum DESC
LIMIT 1
)t1
SELECT p.product_name
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
WHERE o.product_id IS NULL;
SELECT SUM(o.quantity * p.price) AS total_sum
FROM orders o
JOIN buyers b ON o.buyer_id = b.buyer_id
JOIN products p ON o.product_id = p.product_id
WHERE b.city = 'Москва' AND o.order_date BETWEEN '2024-01-01' AND '2024-12-31';
Forwarded from Поступашки - ШАД, Стажировки и Магистратура
