0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for software quality control experts, automation engineers, and software testers

-- SQL Query to fetch closed questions with specific tags and their best answers

SELECT
    q.Id AS [Post ID],
    q.Title AS [Summary],
    q.Score AS [Question Score],
    q.ViewCount,
    STRING_AGG(t.TagName, ', ') AS [Tags],
    q.Body AS [Question],
    a.Score AS [Answer Score],
    a.Body AS [Answer],
    CONCAT('https://stackoverflow.com/questions/', q.Id) AS [Url]
FROM 
    Posts q
INNER JOIN 
    PostTags pt ON q.Id = pt.PostId
INNER JOIN 
    Tags t ON pt.TagId = t.Id
LEFT JOIN 
    Posts a ON q.AcceptedAnswerId = a.Id
WHERE 
    q.ClosedDate IS NOT NULL 
    AND q.PostTypeId = 1  -- Question
    AND t.TagName IN ('Activation Function', 'Artificial Neural Network', 'ANN', 'Autoencoder', 'Backpropagation', 'Batch Normalization', 'Convolutional Neural Network', 'CNN', 'Cost Function', 'Deep Belief Network', 'DBN', 'Deep Learning', 'Dropout', 'Epoch', 'Fully Connected Layer', 'Generative Adversarial Network', 'GAN', 'Gradient Descent', 'Hyperparameter Tuning', 'tuning', 'Learning Rate', 'Long Short-Term Memory', 'LSTM', 'Loss Function', 'Max Pooling', 'Mini-Batch Gradient Descent', 'Multilayer Perceptron', 'MLP', 'Neural Network', 'Overfitting', 'Recurrent Neural Network', 'RNN', 'Rectified Linear Unit', 'ReLU', 'Residual Network', 'ResNet', 'Softmax', 'Stochastic Gradient Descent', 'SGD', 'Tensor', 'Transfer Learning', 'Vanishing Gradient Problem', 'Weight Initialization', 'Attention Mechanism', 'BERT', 'Bidirectional Encoder Representations from Transformers', 'Capsule Network', 'CNN', 'Convolutional Neural Network', 'DNN', 'Deep Neural Network', 'Embedding', 'Encoder-Decoder Architecture', 'Fine-Tuning', 'Gated Recurrent Unit', 'GRU', 'Gradient Clipping', 'Graph Neural Network', 'GNN', 'L1 Regularization', 'L2 Regularization', 'Layer Normalization', 'Leaky ReLU', 'Learning Rate Scheduler', 'Momentum', 'Neural Architecture Search', 'NAS', 'Nesterov Accelerated Gradient', 'NAG', 'Normalization', 'One-Hot Encoding', 'Positional Encoding', 'Pre-trained Model', 'Reinforcement Learning', 'ReLU', 'Rectified Linear Unit', 'Residual Block', 'RNN', 'Recurrent Neural Network', 'Self-Attention', 'Sequence-to-Sequence', 'Seq2Seq', 'Siamese Network', 'Sigmoid Activation', 'Spectral Normalization', 'Sparse Coding', 'Sparsity', 'Stochastic Depth', 'Support Vector Machine', 'SVM', 'Swish Activation', 'Temporal Convolutional Network', 'TCN', 'Tokenization', 'Transformer', 'U-Net', 'Upsampling', 'Variational Autoencoder', 'VAE', 'Weight Decay', 'Xavier Initialization', 'Zero Padding', 'AdaGrad', 'Adam Optimizer', 'AdamW Optimizer', 'Adversarial Training', 'Attention Layer', 'Bidirectional LSTM', 'Block Dropout', 'Causal Convolution', 'Char-RNN', 'Curriculum Learning', 'Data Augmentation', 'Data Pipeline', 'Denoising Autoencoder', 'DAE', 'Dense Layer', 'DropConnect', 'ELMo', 'Embeddings from Language Models', 'Keras', 'Keras Layers', 'Keras Callbacks', 'Keras Sequential Model', 'Keras Functional API', 'ModelCheckpoint', 'EarlyStopping', 'TensorFlow', 'TensorBoard', 'Keras Optimizers', 'Keras Losses', 'Keras Metrics', 'Keras Model Subclassing', 'Keras Custom Layers', 'Keras Model Compilation', 'Keras Fit', 'Keras Evaluate', 'Keras Predict', 'fit', 'predict')
GROUP BY
    q.Id, q.Title, q.Score, q.ViewCount, q.Body, a.Score, a.Body
HAVING 
    COUNT(DISTINCT t.TagName) >= 1
ORDER BY 
    q.Score DESC
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY;

fork query

edited aug 12

created jul 5 anonymous