Blog
Quick Look: Sales Ops and Developers using ChatGPT CHATGPT SAVES TIME
More time for the gym?
I had lunch with one of my friends who has a full time job where one of his responsibilities is sales operation. He uses Tableau, Excel and numerous other tools. He mentioned that he sometimes uses ChatGPT to generate SQL queries for data he needs to extract from the Sales DBMS. In the past he would do this without ChatGPT but in adopting ChatGPT to help him out he is able to get to the end results quicker. A star performer I am sure he will use this free time wisely, whereas others may lengthen their time at the local gym.
The examples below illustrate performing this task both in a SQL as well generating code for a Node.js application
#1 Here is an example asking for a SQL solution
My chatbot prompt
Q: Hi Wizard, I need the SQL commands to analyze sales by region and have the top 10 sales people for each region displayed by order of total sales, ascending to descending. For the top 5 I want the * symbol displayed after the name.
A:
Response –
Sure! Assuming you have a table called "sales" that includes columns for "region", "salesperson", and "sales_amount", you can use the following SQL query to achieve your desired output:
SELECT region,
CASE
WHEN salesperson IN (
SELECT salesperson
FROM sales
WHERE region = s.region
GROUP BY salesperson
ORDER BY SUM(sales_amount) DESC
LIMIT 5
)
THEN CONCAT(salesperson, '*')
ELSE salesperson
END AS salesperson,
SUM(sales_amount) AS total_sales
FROM sales s
GROUP BY region, salesperson
HAVING total_sales > 0
ORDER BY region ASC, total_sales DESC;
Let me explain how this query works:
The first line of the query selects the "region" column from the "sales" table.
The second line uses a CASE statement to add an asterisk (*) to the end of the "salesperson" column if they are one of the top 5 salespeople in their region. To determine the top 5, we use a subquery that groups the salespeople by name, sums up their sales amounts, and sorts them in descending order. We then limit the subquery to the first 5 results.
The third line calculates the sum of sales amounts for each salesperson in each region, using the SUM function.
The fourth line groups the results by region and salesperson.
The fifth line filters out any rows where the total sales amount is zero.
Finally, the results are ordered by region in ascending order, and then by total sales amount in descending order.
I hope this helps! Let me know if you have any questions.
“This content was generated using OpenAI Playground1
#2 Here is request for the business problem above but in Node.js (note that I was specific in asking for what I wanted and did not want to be used)
My chatbot prompt:
Q: Using Node.JS create the code for the NoSQL database Azure Cosmos DB when you returning totaling sales bookings within the last 30 days for each personId. Return the results in descending order by total sales. For the personId that represents the top 5 of all bookings, add the "*" at the end of the personName. For any errors, call the routine CustomErrorHandler. Comment the code at a detailed level. Database name is myDB. Use the aggregate function. Do not use SQL commands
A:
""""
Response:
// First, import the necessary modules for working with the Azure Cosmos DB
const CosmosClient = require("@azure/cosmos").CosmosClient;
const config = require("./config");
// Create a new instance of the Cosmos Client
const client = new CosmosClient({
endpoint: config.endpoint,
auth: {
masterKey: config.primaryKey
}
});
// Get the database and collection references
const database = client.database("myDB");
const container = database.container("items");
// Call the aggregate function to get the total sales within the last 30 days for each personId
container.items
.aggregate([
// Group by personId
{
"$group": {
"_id": {
"personId": "$personId"
},
// Sum total sales
"totalSales": {
"$sum": "$sales"
}
}
},
// Sort by totalSales in descending order
{
"$sort": {
"totalSales": -1
}
},
// Limit the results to the top 5
{
"$limit": 5
},
// Add the * character to the end of the personName
{
$project: {
"_id": 0,
"personId": "$_id.personId",
"personName": {
$concat: ["$_id.personName", "*"]
},
"totalSales": 1
}
}
])
.toArray()
.then(result => {
console.log(result);
})
// Catch any errors and call the CustomErrorHandler
.catch(err => {
CustomErrorHandler(err);
});
// CustomErrorHandler is a user-defined function for handling errors
function CustomErrorHandler(err) {
// Handle the error here
console.log(err);
}
""""
#3 ChatGPT’s ability to generate code - effortless, but is it Production quality code?
An easy way to experience what this talk about code generation is all about...let's generate the code for a common development task which is a password reset feature for an application. This is an important feature and very commonly implemented so I thought this would be a good test.
Informing the chatbot of the code I wanted it to generate was effortless.
I first thought of how I would talk to a fellow developer about what type of code I wanted (node.js) and the functionality (enabling a user to reset their password), then typed it into the chat window and participated in the simple conversation that then took place with the bot. A few questions I was “asked” were about what DBMS and email package I planned to use. It was a straightforward chat session with a good user experience. Overall, I found this session similar to brainstorming with another developer
Me - “Write code in node.js that for a typical application would allow people to reset their password”
Response - “Sure, I can help you with that. To write code in node.js that would allow people to reset their password, you need to follow these steps:
Validate the user’s email address and generate a reset token 123.
Send an email to the user with a link that contains the reset token 14.
Create a route that handles the password reset request and verifies the token 45.
Update the user’s password in your database and invalidate the token 46.
Here is an example of how to generate a reset token using crypto module 3:
userSchema.methods.createPasswordResetToken = function () {
const resetToken = crypto.randomBytes(32).toString('hex');
this.passwordResetToken = crypto.createHash('sha256').update(resetToken).digest('hex');
// Please note that you need to specify a time to expire this token
return resetToken;
};
Do you want me to show you more code examples for each step?
“
The value of the code it generated. The code provided was good early beta type quality as it was lacking some needed error handling and was coded in a way not exactly similar to how I add methods to my app and so on. The real value of the generated code was that it got me kick-started very quickly thinking about how I would build this solution for a production release and saved time doing my own research on sites such as stackoverflow. And, including the links to the 3rd party content that helped produce the response was great.
Click here if you want to see the code generated by the chatbot.
Step 1
This code validates the user’s email address and generates a reset token using node.js, mongodb, and crypto modules.
Sure, I can show you the code for step 1. Here it is:
```js
// Require modules
const crypto = require("crypto");
const mongoose = require("mongoose");
// Define user schema
const userSchema = new mongoose.Schema({
email: {
type: String,
required: true,
unique: true,
},
password: {
type: String,
required: true,
},
resetToken: {
type: String,
},
resetTokenExpiration: {
type: Date,
},
});
// Define user model
const User = mongoose.model("User", userSchema);
// Define a function to validate email and generate token
async function validateEmailAndGenerateToken(email) {
// Find user by email
const user = await User.findOne({ email });
// If no user found, throw an error
if (!user) {
throw new Error("No account with that email found.");
}
// Generate a random token using crypto module
const token = crypto.randomBytes(32).toString("hex");
// Set the token and expiration date on the user document
user.resetToken = token;
// Set expiration date to one hour from now
user.resetTokenExpiration = Date.now() + (60 *60 *1000);
// Save the updated user document
await user.save();
// Return the token
return token;
}
```
This code validates the user's email address and generates a reset token using node.js, mongodb, and crypto modules. Do you have any questions about this code?
Source: Conversation with Bing, 2/19/2023(1) Nodemailer :: Nodemailer. https://nodemailer.com/ Accessed 2/19/2023.
(2) NodeMailer Example: Learn How to Send Emails from NodeJS App - Bacancy. https://www.bacancytechnology.com/blog/send-email-using-nodemailer Accessed 2/19/2023.
(3) How to Use Nodemailer to Send Emails from Your Node.js Server. https://www.freecodecamp.org/news/use-nodemailer-to-send-emails-from-your-node-js-server/ Accessed 2/19/2023.
(4) How to create an api that sends mails using node and mongodb. https://stackoverflow.com/questions/59908589/how-to-create-an-api-that-sends-mails-using-node-and-mongodb Accessed 2/19/2023.
(5) nodemailer - npm. https://www.npmjs.com/package/nodemailer Accessed 2/19/2023.
Step 2
Sure, I can show you the code for step 2. Here it is:
```js
// Require modules
const nodemailer = require("nodemailer");
// Create a transporter object using your SMTP credentials
const transporter = nodemailer.createTransport({
host: "smtp.example.com",
port: "465",
secureConnection: true,
auth:{
username:"yourusername@example.com",
password:"yourpassword"
}
});
// Define a function to send an email with a reset link
async function sendResetEmail(email, token) {
// Create an email message object with options
let message = {
from:"yourusername@example.com",
to:`${email}`,
subject:"Password Reset",
text:`You requested a password reset. Click on this link to set a new password:
http://localhost/reset/${token}`
};
// Send the message using transporter object
let info = await transporter.sendMail(message);
// Log some info about sent message
console.log(`Message sent successfully as ${info.messageId}`);
}
```
This code sends an email to the user with a link that contains the reset token using node.js and nodemailer module. Do you have any questions about this code?
Source: Conversation with Bing, 2/19/2023(1) Nodemailer :: Nodemailer. https://nodemailer.com/ Accessed 2/19/2023.
(2) NodeMailer Example: Learn How to Send Emails from NodeJS App - Bacancy. https://www.bacancytechnology.com/blog/send-email-using-nodemailer Accessed 2/19/2023.
(3) How to Use Nodemailer to Send Emails from Your Node.js Server. https://www.freecodecamp.org/news/use-nodemailer-to-send-emails-from-your-node-js-server/ Accessed 2/19/2023.
(4) How to create an api that sends mails using node and mongodb. https://stackoverflow.com/questions/59908589/how-to-create-an-api-that-sends-mails-using-node-and-mongodb Accessed 2/19/2023.
(5) nodemailer - npm. https://www.npmjs.com/package/nodemailer Accessed 2/19/2023.
Step 3
Create a route that handles the password reset request and verifies the reset token:
// Require modules
const express = require("express");
const User = require("./user"); // Import your User model
// Create an express app
const app = express();
// Define a route for password reset request
app.get("/reset/:token", async (req, res) => {
// Get the token from request parameters
const {token} = req.params;
// Find a user document that has this token and is not expired
const user = await User.findOne({resetToken :token, resetTokenExpiration :{$gt :Date.now()}});
// If no such user found, send an error response
if (!user) {
return res.status(404).send("Invalid or expired token.");
}
// Otherwise, render a form for entering new password (you need to create this view)
res.render("new-password", {userId :user._id.toString(), passwordToken :token});
});
Step 4
Update the user’s password in the database and invalidate the reset token:
// Require modules
const bcrypt= require("bcryptjs");
const User= require("./user"); // Import your User model
// Define another route for updating password after submitting form (use POST method)
app.post("/new-password", async (req,res)=>{
try{
const {userId,password,passwordConfirm,passwordToken}=req.body;
if(password!==passwordConfirm){
return res.status(400).send("Passwords do not match.");
}
const hashedPassword=await bcrypt.hash(password,12);
const updatedUser=await User.findOneAndUpdate(
{ _id :userId ,resetToken :passwordToken ,resetTokenExpiration :{$gt :Date.now()}},
{$set:{password :hashedPassword ,resetToken:null ,resetTokenExpiration