I want to grab the first returned record and previous record based on a user input date. Example, I put in 7/5/22, and I want to grab that date’s food value and somehow include the record previous to that, so output would be: If I put in 3/16/22, then I want my output to be: My totally wrong code: Answe…
Tag: analytics
Three-table join with NO EXISTS
I have three tables and would like to answer the following question in SQL: “Who has only certifications that do NOT have scores?” For instance, in the setup below, the query would return “John” only. Joana has the “AWS Certification”, which is in SCORE table (id 57). Marry…
Scenarios andsSteps “get last change” problem
I have following table abstracting some scenarios and their steps ordered by START_DATE “desc”. SCENARIO_KEY STEP_KEY START_DATE END_DATE PREVIOUS_SCENARIO_KEY PREVIOUS_STEP_KEY 128 44 20xx04x4 0 120 44 120 44 20xx03x3 20xx04x4 120 38 120 38 20xx11x0 20xx03x3 121 38 121 38 20xx07x2 20xx11x0 120 44…
Determine persistent growth in value during a specific time range
Given the following data set example, how should I structure my SQL query to determine if the value has gradually grown day-by-day given a time range in the query for a specific user_id by returning a …
Determine growth in value in a specific time range
Given the following data set example, how should I structure my SQL query in order to determine if the value has grown over time (given a time range in the query) for a specific UserId by returning either a positive/negative growth percentage result or a true/false result UserId timestamp value 1 1617711825 3…
How do i write this in python and preferably in pandas?(Assume that i am dealing with a dataframe)
This is the code that i am trying to convert to Pandas: Assume the following column for input data frame: geo | region | sub region | txn_date | revenue | profit. Columns in output dataframe : geo | region | ytd_rev | py_ytd_rev| total_profit Answer I believe you need GroupBy.agg with named aggregation and ne…
mySQL – How can I see which users submitted orders last month but not this month?
I need to figure out the users who submitted orders last month but not this month. This is my current query to count total users, but how can I cross check to see who has bought this month but not the last? This is my the customer orders table this would be the expected output, a list of all the
How to save SQL query analytics in the background while still delivering records to user?
I am interested in knowing if there is an efficient way to record the analytics from a SQL query while still providing the records to the requester? I’m currently on MS SQL Server 2012. Let’s say for …
Python Pandas non equal join
Have table OUT Need: so i need make non equal join equivalent SQL query : or as SQL query: The problem in PANDAS is that NON EQUAL SELF JOIN cannot be done with MERGE. And I can’t find another way….. Answer We can solve this in pandas in a smarter way by using groupby with agg and joining the stri…
In SQL how do I group by every one of a long list of columns and get counts, assembled all into one table?
I have performed a stratified sample on a multi-label dataset before training a classifier and want to check how balanced it is now. The columns in the dataset are: |_Body|label_0|label_1|label_10|…