How well do popular SQL Database Management Systems stack up against each other in 2024?
I have been using SQLite for most of my time at my University and recently have been looking into the benefits of MySQL but I wanted to know what are the pros/cons of other SQL dtb management systems.
Cuales son las consultas mas utilizadas en SQL?
Estoy iniciando en la incursión por el lenguaje SQL y quisiera conocer cuales son los comandos mas utilizados por la comunidad. Estoy realizando consultas básicas, pero quiero ampliar mis conocimientos.
Leetcode SQL that I think is right! yet wrong
WITH cte AS (
SELECT mr.movie_id, mr.user_id, mr.rating, m.title, u.name, mr.created_at
FROM Users u
JOIN MovieRating mr ON u.user_id = mr.user_id
JOIN Movies m ON mr.movie_id = m.movie_id
)
SELECT results
FROM (
(
SELECT name AS results
FROM cte
GROUP BY user_id, name
HAVING COUNT() = (
SELECT MAX(cnt)
FROM (
SELECT COUNT() AS cnt
FROM cte
GROUP BY user_id
) AS subquery1
)
ORDER BY name ASC
LIMIT 1
)
UNION ALL
(
SELECT title AS results
FROM cte
WHERE YEAR(created_at) = 2020 AND MONTH(created_at) = 2
GROUP BY movie_id, title
HAVING AVG(rating) = (
SELECT MAX(rate) AS max_rate
FROM (
SELECT AVG(rating) AS rate
FROM cte
WHERE YEAR(created_at) = 2020 AND MONTH(created_at) = 2
GROUP BY movie_id
) AS subquery2
)
ORDER BY title ASC
LIMIT 1
)
) AS final_results; —- MovieRating =
| movie_id | user_id | rating | created_at |
| ——– | ——- | —— | ———- |
| 1 | 1 | 3 | 2020-02-16 |
| 1 | 2 | 5 | 2020-02-22 |
| 1 | 7 | 4 | 2020-01-26 |
| 1 | 13 | 1 | 2020-03-04 |
| 2 | 1 | 5 | 2020-01-29 |
| 2 | 3 | 5 | 2020-02-04 |
| 2 | 5 | 1 | 2020-02-03 |
| 2 | 6 | 1 | 2020-02-28 |
| 2 | 7 | 3 | 2020-02-12 |
| 2 | 8 | 2 | 2020-01-29 |
| 2 | 12 | 1 | 2020-02-24 |
| 3 | 2 | 2 | 2020-01-25 |
| 3 | 6 | 4 | 2020-02-27 |
| 3 | 7 | 1 | 2020-02-15 |
| 3 | 9 | 4 | 2020-02-28 |
| 3 | 14 | 3 | 2020-02-15 |
| 4 | 2 | 4 | 2020-01-28 |
| 4 | 4 | 2 | 2020-01-29 |
| 4 | 5 | 1 | 2020-01-26 |
| 4 | 6 | 2 | 2020-02-18 |
| 4 | 7 | 5 | 2020-02-23 |
| 4 | 8 | 4 | 2020-02-17 |
| 4 | 10 | 1 | 2020-01-29 |
| 4 | 11 | 1 | 2020-03-01 |
| 4 | 12 | 1 | 2020-01-26 |
| 5 | 3 | 1 | 2020-02-12 |
| 5 | 11 | 5 | 2020-01-30 |
| 5 | 14 | 1 | 2020-02-11 |
| 6 | 1 | 1 | 2020-02-07 |
| 6 | 2 | 1 | 2020-02-01 |
| 6 | 7 | 4 | 2020-02-08 |
| 6 | 11 | 2 | 2020-01-26 |
| 6 | 13 | 1 | 2020-02-12 |
| 7 | 4 | 4 | 2020-02-18 |
| 7 | 7 | 3 | 2020-01-29 |
| 7 | 8 | 1 | 2020-02-29 |
| 7 | 9 | 3 | 2020-01-26 |
| 7 | 12 | 1 | 2020-02-05 |
| 7 | 14 | 3 | 2020-01-30 |
| 8 | 2 | 4 | 2020-02-25 |
| 8 | 3 | 5 | 2020-03-02 |
| 8 | 5 | 5 | 2020-02-27 |
| 8 | 6 | 5 | 2020-02-08 |
| 8 | 10 | 4 | 2020-01-29 |
| 8 | 11 | 4 | 2020-01-30 |
| 9 | 4 | 5 | 2020-02-11 |
| 9 | 9 | 3 | 2020-02-26 |
| 9 | 11 | 5 | 2020-03-04 |
| 10 | 1 | 5 | 2020-01-26 |
| 10 | 7 | 5 | 2020-02-21 |
| 10 | 13 | 4 | 2020-02-17 |
| 11 | 2 | 3 | 2020-02-15 |
| 11 | 5 | 3 | 2020-03-02 |
| 11 | 11 | 3 | 2020-01-30 |
| 11 | 12 | 5 | 2020-02-06 |
| 12 | 1 | 4 | 2020-03-04 |
| 12 | 4 | 5 | 2020-02-10 |
| 12 | 5 | 3 | 2020-02-03 |
| 12 | 6 | 1 | 2020-01-26 |
| 12 | 11 | 5 | 2020-02-14 |
SQL that I think is right! yet wrong
WITH cte AS (
SELECT mr.movie_id, mr.user_id, mr.rating, m.title, u.name, mr.created_at
FROM Users u
JOIN MovieRating mr ON u.user_id = mr.user_id
JOIN Movies m ON mr.movie_id = m.movie_id
)
SELECT results
FROM (
(
SELECT name AS results
FROM cte
GROUP BY user_id, name
HAVING COUNT() = (
SELECT MAX(cnt)
FROM (
SELECT COUNT() AS cnt
FROM cte
GROUP BY user_id
) AS subquery1
)
ORDER BY name ASC
LIMIT 1
)
UNION ALL
(
SELECT title AS results
FROM cte
WHERE YEAR(created_at) = 2020 AND MONTH(created_at) = 2
GROUP BY movie_id, title
HAVING AVG(rating) = (
SELECT MAX(rate) AS max_rate
FROM (
SELECT AVG(rating) AS rate
FROM cte
WHERE YEAR(created_at) = 2020 AND MONTH(created_at) = 2
GROUP BY movie_id
) AS subquery2
)
ORDER BY title ASC
LIMIT 1
)
) AS final_results; —- MovieRating =
| movie_id | user_id | rating | created_at |
| ——– | ——- | —— | ———- |
| 1 | 1 | 3 | 2020-02-16 |
| 1 | 2 | 5 | 2020-02-22 |
| 1 | 7 | 4 | 2020-01-26 |
| 1 | 13 | 1 | 2020-03-04 |
| 2 | 1 | 5 | 2020-01-29 |
| 2 | 3 | 5 | 2020-02-04 |
| 2 | 5 | 1 | 2020-02-03 |
| 2 | 6 | 1 | 2020-02-28 |
| 2 | 7 | 3 | 2020-02-12 |
| 2 | 8 | 2 | 2020-01-29 |
| 2 | 12 | 1 | 2020-02-24 |
| 3 | 2 | 2 | 2020-01-25 |
| 3 | 6 | 4 | 2020-02-27 |
| 3 | 7 | 1 | 2020-02-15 |
| 3 | 9 | 4 | 2020-02-28 |
| 3 | 14 | 3 | 2020-02-15 |
| 4 | 2 | 4 | 2020-01-28 |
| 4 | 4 | 2 | 2020-01-29 |
| 4 | 5 | 1 | 2020-01-26 |
| 4 | 6 | 2 | 2020-02-18 |
| 4 | 7 | 5 | 2020-02-23 |
| 4 | 8 | 4 | 2020-02-17 |
| 4 | 10 | 1 | 2020-01-29 |
| 4 | 11 | 1 | 2020-03-01 |
| 4 | 12 | 1 | 2020-01-26 |
| 5 | 3 | 1 | 2020-02-12 |
| 5 | 11 | 5 | 2020-01-30 |
| 5 | 14 | 1 | 2020-02-11 |
| 6 | 1 | 1 | 2020-02-07 |
| 6 | 2 | 1 | 2020-02-01 |
| 6 | 7 | 4 | 2020-02-08 |
| 6 | 11 | 2 | 2020-01-26 |
| 6 | 13 | 1 | 2020-02-12 |
| 7 | 4 | 4 | 2020-02-18 |
| 7 | 7 | 3 | 2020-01-29 |
| 7 | 8 | 1 | 2020-02-29 |
| 7 | 9 | 3 | 2020-01-26 |
| 7 | 12 | 1 | 2020-02-05 |
| 7 | 14 | 3 | 2020-01-30 |
| 8 | 2 | 4 | 2020-02-25 |
| 8 | 3 | 5 | 2020-03-02 |
| 8 | 5 | 5 | 2020-02-27 |
| 8 | 6 | 5 | 2020-02-08 |
| 8 | 10 | 4 | 2020-01-29 |
| 8 | 11 | 4 | 2020-01-30 |
| 9 | 4 | 5 | 2020-02-11 |
| 9 | 9 | 3 | 2020-02-26 |
| 9 | 11 | 5 | 2020-03-04 |
| 10 | 1 | 5 | 2020-01-26 |
| 10 | 7 | 5 | 2020-02-21 |
| 10 | 13 | 4 | 2020-02-17 |
| 11 | 2 | 3 | 2020-02-15 |
| 11 | 5 | 3 | 2020-03-02 |
| 11 | 11 | 3 | 2020-01-30 |
| 11 | 12 | 5 | 2020-02-06 |
| 12 | 1 | 4 | 2020-03-04 |
| 12 | 4 | 5 | 2020-02-10 |
| 12 | 5 | 3 | 2020-02-03 |
| 12 | 6 | 1 | 2020-01-26 |
| 12 | 11 | 5 | 2020-02-14 |
how to sample time stamped records
I have a data set that has a timestamp column. What I’m looking for is a select statement that will allow me to collect a result set that contains rows from every x seconds. For example, consider this table: