Questions tagged [sql]
Structured Query Language is a language for interacting with relational databases. Read the tag wiki's guidelines for requesting SQL reviews: 1) Provide context, 2) Include the schema, 3) If asking about performance, include indexes and the output of EXPLAIN SELECT.
1,461
questions
4
votes
2
answers
662
views
A schema for awallet system that allows transfers between users
I am currently working on a wallet system that allows transfers of money between users. I tried creating this as a Stack Overflow question, but it was rejected. I'm not sure if this is the right ...
- 153
3
votes
2
answers
154
views
Clean up verbose code for Oracle SQL
I have the following Oracle SQL code that works, but I would like to try and find a way to clean it up so it's not so repetitive. It is looking at orders for a part ordered each FY in an Oracle ...
- 33
3
votes
1
answer
133
views
Faster Query to Track State changes in a Large Table
I have a table that tracks state changes of an entity with timestamps. The schema of the table is as follows:
entityid
userid
originalvalue
newvalue
changetime
255
101
Draft
Submitted
2023-11-28 12:...
- 53
2
votes
1
answer
63
views
Delete duplicates from SQL database with Python and reset ID's
I tried to create a peice of code that helps me remove duplicates from my sql database and reset the ID's as well.
I am not sure that it's error proof.
...
- 127
2
votes
1
answer
58
views
Postgresql generated column from Finnish personal identity code
The Finnish personal identity code is defined as:
Day, month, and year of birth, in ddmmyy form (six digits, zero padded if necessary)
A separator
For those born &...
- 751
2
votes
0
answers
42
views
Case Study: Database design help for a small app used to coach tennis players
I have a small SQL server database and VBA program that is used to automate tasks related to tennis students, programs, practices, performances, and student testing.
I'm trying to learn db design and ...
- 21
0
votes
1
answer
80
views
Feedback for my first project - sqlserver-json-tools
could you review my first project on github please. The JSON_MODIFY function in SQL Server 2016 and 2019 does not allow creating paths dynamically, making it necessary to chain JSON_MODIFY executions. ...
2
votes
1
answer
83
views
Determining the least dangerous tags with over 1K answers to answer
So I thought I'd take another shot at making a SEDE query after being somewhat inactive after a while.
Essentially what this does is that it takes the tags that have at least 1000 answers for that tag ...
- 279
3
votes
1
answer
56
views
PostgreSQL Search Feature
The Problem
I am developing a search feature in PostgreSQL that involves a collection of JSONB documents stored in a table, which have been standardised. The goal is to enable clients to perform ...
- 63
1
vote
1
answer
27
views
Filter filtered bike prices twice without repeating yourself
dbfiddle
Goal
Without repetition in the code, I want to filter BikeValuationList to only give the BikePriceId of the latest ...
- 127
0
votes
0
answers
20
views
Calendar Table in Power BI
I've been reusing calendar tables in various formats for a few years now. I'd like to know best practices and if there are ways to improve them. Any suggestions for style, code, etc. are all welcome. ...
- 490
1
vote
0
answers
81
views
To-do app with TypeScript backend (violations of TS best practices or unidiomatic code)
The working code in full is here.
It's a To-Do App with a TypeScript backend, written as practice for an upcoming 4-hour interview for a TypeScript backend role. My colleague and I would like to know, ...
2
votes
1
answer
118
views
Need assistance in Optimizing the Recursive Query in Pyspark
I am trying to convert a SQL Server stored procedure to Pyspark code.
This is my SQL Server query:
...
- 21
3
votes
1
answer
101
views
Creating an array (temporary table) in SQL and iterating through each row within this table to preform a check on the data in each row
The SQL Server version I am using is SQL Server 2008.
I've got an SQL query/script I've written which is to help simplify some things while our API is being rebuilt.
Essentially, this script allows ...
- 131
3
votes
2
answers
106
views
Calculate the count of top performers in SQL
I have a transaction table let's call it Offers Table. Please don't read too much into the business but let's say a customer wants to buy a car and the customer searches variety of websites but at the ...
- 31
6
votes
1
answer
904
views
Show how close a user is to being unsung
So this morning, I decided to create a SEDE query that answers the age-old question "How close am I to being Unsung?" because I recently polished up my first SEDE query and I realized that I ...
- 279
2
votes
1
answer
40
views
Understanding how to properly create a stored procedure that only does INSERT from sourcetable to targettable with dynamic sql
Background and use-case
I need to create a stored procedure where the input parameters will be: @sourceTable, @targetTable, @compositeKey. I'm using available fields for the composite key, since I ...
- 21
2
votes
1
answer
247
views
1
vote
0
answers
39
views
insert then update generated id back to the table on bulk insert using XML in SQL server
I have 2 tables StockDetails, StockMail
First insert Stock name and Expiration Date into StockDetails Table(inserting bulk data using XML)
Then Generate Reminder date(7 Days before expiration date) ...
- 131
2
votes
2
answers
148
views
SQLite querybuilder class in python
Motivated by the first commentor's suggestion on my MSAccess question:
Python SQL insert to MSAccess with VBScript
I am moving to use SQLite databases for a python application and I created this query ...
- 103
3
votes
3
answers
115
views
Improving efficiency of database searches under SQLite (SpatiaLite - QGIS)
I'm new to SQL, but I've always heard that it's a very efficient way of searching databases. For the same task, in VBA I get a result in a few seconds for 1000 queries. In SQL it takes several minutes....
- 159
4
votes
1
answer
100
views
Python SQL insert to MSAccess with VBScript
I have a business use case that required running SQL insert queries to a local MSAccess database with a Python script. The actual script does many other things and the SQL function is just one of them,...
- 103
3
votes
1
answer
96
views
SQL query creating tables needed by a simple PHP CMS
I have written recently the SQL query to MariaDB that I want to move into PHP code. Below is my test.sql file. What do you think about my SQL code?
...
- 441
0
votes
0
answers
25
views
Prisma models with various relations
The code is a bunch of Prisma models, starting from Move. This model stores data about pokémon moves as can be obtained from the PokéAPI. Further models are shown ...
- 43
0
votes
1
answer
43
views
Find and conditionally sort all venues matching a filter provided by an API client in PostgreSQL
I have an API where users can retrieve a list of "venues".
Every venue is a profile, but not every profile is a venue
Venues can be "parents" of other venues (think hotel - floor -...
- 103
4
votes
2
answers
222
views
Querying for vendors and categories
I'm looking for pointers on the best way to optimize my code below. The query is designed to pull for a vendor database in a separate SQL server table that I built a connection to in my wordpress site....
- 141
0
votes
1
answer
64
views
2
votes
1
answer
71
views
Find All Recent Winners and Calculate an Encoding of What They Won
Context
I was proud of this code for a little while, but the repetition wounds me. I know that the function name sucks, but the name and interface to this function are not under my control. ...
- 127
2
votes
2
answers
87
views
Find All Recent Winners and Calculate an Encoding
Context
I was proud of this code for a little while, but the repetition wounds me. I know that the function name sucks, but the name and interface to this function are not under my control. ...
- 127
0
votes
0
answers
19
views
specify a policy for allowing access to a table through a foreign key
Consider the following two tables in a PostgreSQL database:
...
- 101
1
vote
2
answers
28
views
Creating single table vs creating multiple table
I have lots of services which I am integrating. (also I am new to sql)
For each service integration I get different response,
For example, for zoom, I would just need to store the access_Token, for ...
- 495
0
votes
0
answers
20
views
Order Model for a Market Place Ruby on Rails Application
I've created a Marketplace application on ruby on rails and would like to request a review of my code (Order Model) to check if I am in the right track or not...please kindly understand that I am a ...
1
vote
1
answer
132
views
What is the percentage of customers who increase their closing balance by more than 5%?
The Question
What is the percentage of customers who increase their closing balance by more than 5%?
Source Code
The database and all details can be found here.
ERD
My Proposed Solution
...
- 113
-1
votes
1
answer
236
views
Optimizing LINQ Query on C# Web API
Can someone help me optimize this code especially the LINQ query. It takes 8-12 seconds just to return a response when I'm calling the endpoint. Its super slow, and my whole endpoint call is taking ...
0
votes
1
answer
117
views
Is there a more concise way to write this Procedure
I have this stored Procedure that is passed in the data below that will building the SQL select that will get EXEC. The data that can be passed in can me null so I have three ...
- 353
1
vote
1
answer
72
views
Is there a more concise way to write this query in SQL Server?
I'm playing with data from a Time Use survey as a beginner SQL user.
Gender is represented as 1's (Male) and 2's (Female), and the age of each participant is listed. I would like to divide them into ...
- 303
1
vote
1
answer
359
views
Employee Attendance structure design
I am designing a solution to store employee day attendance, which can be recorded either as hours or as an interval within a day. The goal is to maintain a monthly attendance list for each employee.
...
- 119
0
votes
1
answer
31
views
Joining with a different (foreign keys) while using the same key on a different table
I'm a bit new to SQL and I'm still learning how to efficiently use joins. I have two tables.
contains Materials and Alternate Materials
contains Real_ID and Materials.
The code below works however I ...
3
votes
0
answers
90
views
Create table of yearly monthly temperatures from monthly data
I've got a series of tables with monthly data on USA regions over about 130 years, divided by either climate divisions (clim_div) or other federal standards (ncdc_fips). They all follow the same ...
-2
votes
1
answer
83
views
3
votes
1
answer
2k
views
DB2 SQL statement to split a string based on delimiter
I have a "flat file" with structure as below:
...
- 41
3
votes
1
answer
80
views
calculating the percentage of distribution of ids per Loc for each country
Which of the two queries is theoretically better and why?
I would like to understand why query 2 takes longer although there is no relevant information in the Execution Plan.
I'm querying a Presto ...
- 71
1
vote
1
answer
49
views
Find offers that can be matched with bids
I am working on a sql query for the following scenario:
Get all available offers for registered demand (offers from profiles
should not be from profile which had registered demand). After that
get ...
- 121
0
votes
1
answer
164
views
Count how many active and inactive users are in a group/unit/department/team
Given the following example data:
id
username
group
unit
department
team
status
1
user1
g1
u1
d1
t1
active
2
user2
g1
u1
d1
t2
active
3
user3
g1
u1
d1
t3
inactive
4
user4
g3
u6
d12
t30
active
5
...
- 441
2
votes
1
answer
56
views
Update table with data from another table under certain criteria
I need to update a lot of data from one table based on criteria from another table.
My tables and records in those tables look like this:
This is Users table where i can have many records for single ...
- 351
-2
votes
1
answer
58
views
Optimizing a TRANSACT SQL statement [closed]
I'm in need to optimize the following TRANSACT SQL statement :
...
3
votes
0
answers
47
views
SQL query to pivot table into dynamically growing set of columns
I am working on SQL Server I have sample data like this in a table.
CustId
Bank
city
rating
date
rnk
1
Deutsche
Delhi
5
10/10/2022
1
1
BOA
Pune
6
10/10/2022
2
1
UBS
Mumbai
7
10/10/2022
3
1
SBI
...
- 31
1
vote
1
answer
2k
views
15 Days of Learning SQL (HackerRank Challenge) - is having multiple queries a good idea in this case?
The challenge is as follows:
"Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.
Write a query to print total ...
- 635
2
votes
1
answer
63
views
Can this SQL Query for Grouping Multiple Columns and Date be Improved in any way?
I'm trying to refresh my very rusty SQL skills.
I have a table with device logs, collected every 5 minutes. I'm interested in counting the number of times a device is either 'connected' or '...
- 155
2
votes
1
answer
139
views
Extracting authors and books from XML and inserting them into PostgreSQL
There is a tree of start folder, it's subfolders, their subfolders, etc. In each folder, subfolder, etc. there are the same structured XML files stored.
books.xml
...
- 23