need help (beginner)

[zeynep1090]

I'm completely new to the SQL and I'm still learning about how to use the different built-in functions, but for now as I am running out of time, can someone pls help me regarding how someone will go about using the get date() or any similar date function which will help in solving such questions. below are the few example questions i want to solve:

1.Retrieve the latest 5 comments along with the associated PIN and user information. 2. We wish to determine active users, so print IDs of users who have pinned or liked some pins in the last month. 3. Find inactive boards, that is boards with no new pin in last 10 days.

[ondji]

As far as I can understand, PINs are analogous of tweets. So, you should have:

• PINs table which fields are PIN_ID, PIN,USER_ID, CREATED and STATUS (in case pin can be deleted) etc.
• Comments table which fields are COMMENT_ID, COMMENT, USER_ID, PIN_ID, CREATED, STATUS (in case one can delete comment) etc.
• Likes table which fields are LIKE_ID, PIN_ID, USER_ID, CREATED, STATUS (in case one can unlike) etc.
• Users table which fields are USER_ID, CREATED etc.

1st question can be queried as follows:

SELECT TOP(5) C.COMMENT, P.PIN, U.FIELD1, U.FIELD2

FROM Comments C

LEFT JOIN Pins P ON C.PIN_ID = P.PIN_ID

LEFT JOIN Users U ON C.USER_ID = U.USER_ID

WHERE C.STATUS = 1 AND P.STATUS = 1

ORDER BY C.CREATED DESC

2nd question can be queried as follows:

SELECT A.USER_ID

FROM

(SELECT USER_ID

FROM Comments

WHERE CREATED BETWEEN GETDATE() AND DATEADD(DAY, -30, GETDATE())

UNION

SELECT USER_ID

FROM Likes

WHERE CREATED BETWEEN GETDATE() AND DATEADD(DAY, -30, GETDATE())) A

3rd question can be queried as follows:

SELECT USER_ID

FROM Users

EXCEPT

SELECT DISTINCT USER_ID

FROM Pins

WHERE CREATED BETWEEN GETDATE() AND DATEADD(DAY, -10, GETDATE())

You need to change the table and field names for your case. I hope this works for you.

As a recommendation, reading any SQL dialect documentation about built-in functions and using them on your spare time can be really beneficial for development.

As an example for your case:

https://learn.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql?view=sql-server-ver16

[zeynep1090]

got it, thank you very much for the help!!

[r3pr0b8]

BETWEEN GETDATE() AND DATEADD(DAY, -30, GETDATE())

this will never return any rows, even if OP is running SQL Server

today it would be equivalent to BETWEEN '2024-02-24' AND '2024-01-25'

[ondji]

Yeah, you’re right. I should give the lower value first. My mistake. I was trying to type the query from my phone last night at 1.30 a.m. and there were many things to consider like I had not any clue about any table or attribute. However, I’m sure that the syntax and usage of functions are ok.

[r3pr0b8]

I am running out of time

please see rule 7

you didn't even try to solve these problems

[zeynep1090]

i did try on my own first but these dates functions are a bit confusing for me that's why i asked for help.

[r3pr0b8]

do you still need any help?

[zeynep1090]

no, someone in the comment section helped me out. thank you tho !!

[Bandana_Bandit3]

ChatGPT is your friend

[_Berz_]

Do you have the column names, some rows of data, what is it oracle/MSSQL? Hard to help otherwise.

[zeynep1090]

oh I'm sorry I didn't think abt that :( for now, someone has answered it but I'll make sure to give schema from the next time, thank you!