I have the below table: PC A MAXI PC1 A1 1 PC1 A2 2 PC1 A3 3 PC2 A1 1 PC2 A2 2 PC2 A3 3 Is there a way to generate ‘MAXI’ entries for each group of ‘PC’ and ‘A’ in a random order (or at least a seemingly even distribution, even if not random)? Desired output (count_pc would
Tag: sql
How to compare DD/MM dates in BigQuery?
I’m trying to compare two dates without the year, only the day and the month (DD/MM) when for example 30/04 < 02/05. I want to be able to say wether a date is previous or not to another date based only on the day and the month without paying attention to the year. I tried to use FORMAT_DATE(“%d/%m”, DATE(“2021-04-30”)) <=
Oracle SQL – Custom Sort
I have a scenario where I have following data: Table: Locations I want to sort in a way that all the IDs that end in ‘0000’ should be sorted first, then the TYPE ‘Warehouse’ and then the rest of the Stores. The desired output should be like How do I do this custom sorting? Answer This is how I understood
Find cumulative sum of insurance sales amount with increasing date while considering unique salesman names count
I have the following table I want to find the date-wise cumulative SalesAmount, while also maintaining the increasing count of salesman names that come into the table. So basically the task is to eliminate the duplicate names, that may occur on the daily basis, or in a cumulative fashion? Answer You can use a window function to get the first
Query to pull transactions where dispute date exceeds payment date by 120 days
Super new with SQL and just was wanting a nudge in the right direction I have a payment table with column a) payment date and column b) dispute date. I want to pull all transactions where the dispute date exceeds the payment date by 120 days. I think it should have something to do with a CASE function but again
How to grouby data in one column and distribute it in another column in HiveSQL?
I have the following data: CompanyID Department No of People Country 45390 HR 100 UK 45390 Service 250 UK 98712 Service 300 US 39284 Admin 142 Norway 85932 Admin 260 Germany I wish to know how many people belong to the same department from different countries? Required Output Department No of People Country HR 100 UK Service 250 UK 300
How to get top selling items a year ago
I want to get the top selling product of the month last year. Example today’s month is May 2021, so I want to get the top selling products of May 2020, if the month changes to June, it automatically changes the @Date1 to June 1 and @Date2 to June 30. Here is my query for getting the top selling using
Two dependent conditions in exclude DJANGO
I want to check whether the current user already has the same movie id in his personal list or not. If he has it then I want to exclude that movie from my trending list. I want it to be something like this. views.py models.py Answer You can .exclude(…) with: If you specified a related_query_name=… [Django-doc] or a related_name=… [Django-doc],
How do I make an incremental ranking per query system on SQL Server 2019?
Currently on SQL Server 2019, I have the following code: SELECT dbo.GroundTruth.QueryId, dbo.GroundTruth.Q0, dbo.GroundTruth.Id, RANK() OVER(ORDER BY dbo.GroundTruth.Similarity DESC) …
Getting all the values in json array in snowflake
I have a JSON array as follows: This Json data is in the field called col1 in my_table { “JSON_DATA”: { “ID”: 1, “j_array”: [“A”,”B”]…